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.

USE ProCo
GO
CREATE TABLE Movie
(m_id INT PRIMARY KEY,
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:

INSERT INTO Movie
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:

INSERT INTO Movie
VALUES (2,'Bonker Bonzo',75)
INSERT INTO Movie
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.

SELECT * FROM Movie
m_idm_titlem_Runtime

1

AList Explorers

96

2

Bonker Bonzo

75

3

Chumps to Champs

75

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:

INSERT INTO Movie
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.

Rules:

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)

, ,
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

  • Second answer Is Correct

    INSERT into sports values(1,’Football’), (2,’Cricket’)

    Shalini.M
    India

    Reply
  • Sumit R. Santani
    August 9, 2011 1:34 pm

    Answer :2
    City : Mumbai
    Country : India

    Reply
  • Learned something. Wished for this feature often.

    Reply
  • Chirag Satasiya
    August 10, 2011 4:00 pm

    Hi Pinal Sir,

    The correct answer for this question is option no. 2:

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

    Reason:
    We have to insert multiple records with single select statement.
    From above article we can have following syntax for inserting multiple records with single insert statement.
    INSERT INTO TABLE_NAME VALUES(ColName1,ColName2),(ColName1,ColName2)

    In option n. 2 we have used correct syntex with correct parameters.
    All Column values are separated by commas and enclosed into single () brackets.

    Following options are not correct because:
    Option 1:INSERT into tblSports value(1,’Football’,) (2,’Cricket’,)
    Char ‘S’ is missing in values and comma is missing.

    Option 3: INSERT into tblSports values(1,’Football’) (2,’Cricket’)
    Comma is missing between two column values

    Option 4: INSERT into tblSports values(1,’Football’):(2,’Cricket’)
    Semicolon :) is used instead of Comma(,)

    Option 5: INSERT into tblSports values(1,’Football’)::(2,’Cricket’)
    Two Semicolon :) is used instead of Comma(,)

    Regard$
    Chirag Satasiya

    Reply
  • The right answer is #2:

    Insert into tblSports values(1,’Football’), (2,’Cricket’)

    Francisco,
    Miami

    Reply
  • Uday Bhoopalam
    August 16, 2011 9:12 pm

    The second one is correct. It is using the new feature in sql 2008. You need to specify insert once and separete the data for the seond row by a comma delimeter.

    Thank You
    Uday

    Reply
  • Answer is #2:
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Thanks,
    Wayne

    Reply
  • The correct answer is;

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

    Bulent

    Reply
  • answer is # 2:

    I am from USA

    Reply
  • answer is # 2:

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

    In Row constructor each row values will be seperated by “,”.

    Country : India

    Regards
    Mahesh

    Reply
  • Bhargav from surat
    August 17, 2012 3:42 pm

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

    Reply
  • Sriram Nidamanuri
    August 22, 2012 6:31 pm

    Option :2

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

    Reply
  • SERT into tblSports values(1,’Football’), (2,’Cricket’)

    Reply
  • option 2
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Reply
  • Asmita Patel
    June 14, 2017 5:07 pm

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

    this is right answer

    thanks
    Asmita
    from india

    Reply

Leave a Reply

Menu