How Many Rows Will Be Inserted in SQL Server?

One of the common questions asked by beginners while learning SQL Server is:

“How many rows will be inserted when these SQL statements are executed?”

This looks like a simple question, but it helps you understand important SQL concepts such as the INSERT INTO statement, rows and columns, SQL Server data types, implicit data conversion, and how to verify the number of records in a table.

In this article, we will walk through a simple SQL Server example and determine exactly how many rows will be inserted.

Understanding the SQL Server Table

Consider a table named tblTest with three columns:

CREATE TABLE tblTest
(
    col1 INT,
    col2 CHAR(30),
    col3 CHAR(10)
);

The table contains:

• col1 – Integer column
• col2 – Character column with a length of 30
• col3 – Character column with a length of 10

The CREATE TABLE statement only creates the table structure.

It does not insert any rows.

At this stage, the table contains zero records.

What Happens When INSERT Statements Are Executed?

The SQL script contains four separate INSERT statements:

INSERT INTO tblTest VALUES (1, 1, 1);

INSERT INTO tblTest VALUES ('2', '2', '2');

INSERT INTO tblTest VALUES (3, 3, 3);

INSERT INTO tblTest VALUES ('4', '4', '4');

Each statement supplies one set of values.

Therefore, each statement inserts one row.

So, the answer is:

4 rows will be inserted.

Why Does Each INSERT Add Only One Row?

The important part to understand is the VALUES clause.

For example:

INSERT INTO tblTest VALUES (1, 1, 1);

There is one set of values:

(1, 1, 1)

Those three values correspond to the three columns in the table.

Therefore, one record is inserted.

Similarly:

INSERT INTO tblTest VALUES (3, 3, 3);

also inserts exactly one row.

The number of values does not represent the number of rows.

In this example, three values are provided because the table has three columns. The entire set represents one record.

Understanding Rows and Columns

A common beginner mistake is to confuse columns with rows.

Suppose the table looks like this:

There are:

• 3 columns
• 4 rows

Each horizontal record is a row.

Each vertical field is a column.

Therefore, although each INSERT statement contains three values, it inserts only one row.

Why Are Some Values Written Inside Quotes?

You may notice that some values are written like this:

'2'

and others like this:

2

This is related to SQL Server data types.

The first column is defined as:

col1 INT

Therefore, col1 expects an integer value.

For example:

INSERT INTO tblTest VALUES (2, 'SQL', 'DBA');

Here, 2 is an integer.

However, SQL Server can also convert a compatible character value such as:

'2'

into an integer when inserting it into an INT column.

This is called implicit data type conversion.

What Is Implicit Conversion in SQL Server?

Implicit conversion occurs when SQL Server automatically converts one compatible data type into another data type.

For example:

INSERT INTO tblTest VALUES ('2', '2', '2');

The first value '2' is written as a string, but the destination column col1 is an INT.

SQL Server can convert:

'2' → 2

because the value is compatible with the integer data type.

This allows the statement to execute successfully.

However, relying on implicit conversion should be done carefully, especially in production applications, because incompatible values can cause conversion errors.

How Can You Insert Multiple Rows at Once?

It is also possible to insert multiple records using a single INSERT statement.

For example:

INSERT INTO tblTest
VALUES
(10, 'SQL', 'DBA'),
(20, 'Power BI', 'DA'),
(30, 'Azure', 'DE');

In this example, there are three sets of values.

Therefore:

3 rows will be inserted.

This gives us an important rule:

One value set = one row.

So:

(10, 'SQL', 'DBA')       → 1 row
(20, 'Power BI', 'DA')   → 1 row
(30, 'Azure', 'DE')      → 1 row

Total = 3 rows

How to Check the Number of Rows in SQL Server

After inserting the records, you can use:

SELECT * FROM tblTest;

This displays all records in the table.

For the given example, the result will contain four rows.

You can also use the COUNT() function to get the exact number of records.

SELECT COUNT(*) AS TotalRows
FROM tblTest;

The result will be:

TotalRows
---------
4

This is one of the easiest ways to verify how many records currently exist in a table.

A Complete Example to Practice

Here is the complete SQL example:

CREATE TABLE tblTest
(
    col1 INT,
    col2 CHAR(30),
    col3 CHAR(10)
);

INSERT INTO tblTest VALUES (1, 1, 1);

INSERT INTO tblTest VALUES ('2', '2', '2');

INSERT INTO tblTest VALUES (3, 3, 3);

INSERT INTO tblTest VALUES ('4', '4', '4');

SELECT * FROM tblTest;

SELECT COUNT(*) AS TotalRows
FROM tblTest;

After executing the statements, four records will be available in the table.

A Simple Trick for SQL Interview Questions

When you see an SQL interview question asking:

“How many rows will be inserted?”

Don’t count the number of values in the statement.

Instead, identify the number of value sets being inserted.

For example:

INSERT INTO tblTest
VALUES
(1, 'A', 'X'),
(2, 'B', 'Y'),
(3, 'C', 'Z');

There are three value sets.

Therefore:

3 rows will be inserted.

For the uploaded example:

INSERT 1 → 1 row
INSERT 2 → 1 row
INSERT 3 → 1 row
INSERT 4 → 1 row

Total → 4 rows

Common Mistakes Beginners Should Avoid

Mistake 1: Counting the Number of Values

A statement such as:

INSERT INTO tblTest VALUES (1, 1, 1);

contains three values but inserts only one row.

Mistake 2: Counting Columns as Rows

The table has three columns, but that does not mean three rows are inserted.

Mistake 3: Assuming CREATE TABLE Inserts Data

CREATE TABLE only creates the table structure.

It does not add records.

Mistake 4: Ignoring Multiple Value Sets

When multiple value sets are supplied in one INSERT, each value set represents a separate row.

Key SQL Concepts You Should Remember

From this example, you can learn several important SQL Server concepts:

• CREATE TABLE creates a table structure.
• INSERT INTO adds data to a table.
• Each value set represents one row.
• Multiple value sets can be inserted using one INSERT statement.
• SQL Server supports compatible implicit data type conversions.
• SELECT * displays the records.
• COUNT(*) returns the number of rows.
• Rows and columns represent different concepts in a relational database.

Final Answer: How Many Rows Are Inserted?

For the SQL script discussed in this example, there are four individual INSERT INTO statements.

Each statement inserts one row.

Therefore:

Total number of rows inserted = 4

The final table contains:

Answer: 4 rows will be inserted.

Learn SQL with Practical Examples

Understanding simple SQL operations is the foundation for becoming confident with SQL Server.

At SQL School Training Institute – Hyderabad, learners can develop SQL skills through practical queries, real-world database scenarios, assignments, exercises, and hands-on projects.

Learn SQL step by step and strengthen your skills for SQL Developer, Data Analyst, SQL DBA, and other database-related roles.

Trainer: Mr. Sai Phanindra – Founder & Trainer

Website: sqlschool.com

Contact: +91 9666440801 | +91 9951440801

#SQLServer#SQL#SQLQueries#SQLInsert#INSERTInto#SQLTutorial#SQLForBeginners#LearnSQL#SQLServerTutorial#SQLInterviewQuestions#SQLInterview#Database

#DatabaseManagement#SQLDeveloper#SQLDBA#DataAnalytics#DatabaseDeveloper#SQLTraining#SQLPractice#SQLSchool