SQL SERVER – Row Constructors – Day 6 of 35

In this blog post we will learn about Row Constructors.

Row Constructors

Most records we insert will come from a connection made to SQL from some external process. For example a web page ADO.NET connection to your company data layer or some data feed from an SSIS package. Still, most seed data or special inserts may come from the INSERT INTO DML statement. Before SQL 2008 if you had to insert 20 records you needed 20 separate INSERT INTO statements. Now you can do all 20 inserts in one transaction.

SQL SERVER - Row Constructors - Day 6 of 35 rows

Let’s start off our example by creating a very simple table with the following code.

m_title VARCHAR(50) NOT NULL,
m_Runtime INT NULL)

To fill the first record we need to supply four values. In an insert statement you separate each entity value with a comma. To add movie 1 called “AList Explorers,” which is a movie 96 minutes long, you use the following code:

VALUES (1,'AList Explorers',96)

If you want to insert two records at the same time, you always have the option to run multiple INSERT INTO statements. We can insert movie 2 and movie 3 at the same time with two separate statements with the following code:

VALUES (2,'Bonker Bonzo',75)
VALUES (3,'Chumps to Champs',75)

Each statement ran its records once, resulting in two additional records in the Movie table. The old familiar DML statement that starts with the keyword SELECT will show the result set containing the records you inserted. A quick look at all records for the Movie table can confirm how many records you have.

m_id m_title m_Runtime


AList Explorers



Bonker Bonzo



Chumps to Champs


New features are invented so we may discover and use them. Since many of my students works or contract at Microsoft, homework is often done on beta software. One student was tasked to do a double insert like in our last example. She did a great innovative job.

That day the student taught me a new feature in SQL Server 2008 called row constructors. You can do a double insert of data with one INSERT INTO statement using row constructors. Simply separate each group of values with a comma. The row constructor looks exactly like the double INSERT INTO except that you replace subsequent INSERT INTO statements with commas as seen in the code here:

VALUES (4,'Dare or Die',110,'R'), (5,'EeeeGhads',88,'G')

The two records m_id 4 and 5 were successfully inserted into the Movie  table. They were done all at once using the new SQL 2008 feature called row constructors. The first advantage of using row constructors is obvious. You save time by not having to type an additional INSERT INTO statement. The second advantage is that SQL Server uses only one lock instead of two when using the row constructors feature. SQL Server confirms a single transaction of two rows as seen in the “2 row(s) affected” message instead of two “1 row(s) affected” from the earlier example.

Question 6

Q 6) Which code will insert two records with 1 insert into statement?

  • INSERT into tblSports value(1,’Football’,) (2,’Cricket’,)
  • INSERT into tblSports values(1,’Football’), (2,’Cricket’)
  • INSERT into tblSports values(1,’Football’) (2,’Cricket’)
  • INSERT into tblSports values(1,’Football’):(2,’Cricket’)
  • INSERT into tblSports values(1,’Football’)::(2,’Cricket’)

Please post your answer in the comment section to win Joes 2 Pros books.


Please leave your answer in the comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from the United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answering.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

Joes 2 Pros, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35

Related Posts

117 Comments. Leave new

Leave a ReplyCancel reply

Exit mobile version