SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Row Constructors – Day 6 of 35

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 1.
Every day one winner from United States will get Joes 2 Pros Volume 1.

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 you 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.

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_id m_title m_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 work 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 for 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 eariler example.

Note: If you want to setup the sample JProCo database on your system you can watch this video.

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 comment section to win Joes 2 Pros books.

Rules:

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

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

121 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Row Constructors – Day 6 of 35

  1. The right answer is No. 2:
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)
    Because it uses a row constructor where each group of values is separated with a comma.
    Rene Castro
    El Salvador

    Like

  2. The right answer is No. 2:

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

    Because sql server 2008 has row constructor feature in which more than one record is inserted separated by comma within a single insert into statement

    Like

  3. The right answer is No. 2:

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

    Because sql server 2008 has row constructor feature in which more than one record is inserted separated by comma within a single insert into statement

    India

    Like

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

    correct answer :

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

    Shekhar Gurav
    Country – INDIA

    Like

  5. As you have already mentioned above, the syntax of the row constructure is to pass on row values separated by a comma.

    Hence Correct answer is 2nd one :
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Ishan Shah,
    Gandhinagar,
    India

    Like

  6. Correct Answer is:

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

    AS Row Constructor is new feature of SQL Server 2008,
    So you can insert multiple records at at a time with only single Insert into statement with just comma separated constructor

    Thanks.
    Sunny Jagtap
    I’m from INDIA

    Like

  7. Hi Pinal,

    The correct answer is

    INSERT INTO tblSports VALUES (1,’Football’),(2,’Cricket’)

    P. Anish Shenoy,
    INDIA, Bangalore – Karnataka

    Like

  8. Correct answer : – 2

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

    Row constructor concept is used, in which each group of values is separated with a comma.

    India
    Shilpa Sharma

    Like

  9. Option 2 is the correct answer…. all other options are not seperated by comma.

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

    If we want to insert 2 rows at a time, each group of values should be seperated by comma.

    Like

  10. Option 2 is the correct answer…. all other options are not seperated by comma.

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

    If we want to insert 2 rows at a time, each group of values should be seperated by comma.

    By
    Yasodha.N (India)

    Like

  11. The correct option is
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    The row constructor identify the group by , so it inserts the two records successfully.

    country -India

    Like

  12. The correct option is 2.
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    we are supplying two groups of value seperated by a comma using row constructors.
    we are simply seperating each record group by simple comma.
    so the result is two row insert in one go.

    Karan
    India

    Like

  13. Option # 2 will work which has following query

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

    Actually before SQL Server 2008, I was using SELECT…..UNION ALL syntax to insert multiple row but in SQL Server 2008 and higher, I tend to use this Row Constructor which is an ANSI terminology for pseudo table of rows in which you have to separate each group with comma.

    ritesh (India)

    Like

  14. the answer for your Question is

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

    Country : India [Ahmedabad]

    Like

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

    Ans : INSERT into tblSports values(1,’Football’), (2,’Cricket’)
    It works for sql server 2008
    Aditya
    Chennai, INDIA

    Like

  16. Option 2 is correct

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

    Explanation: Row Constructors require different set of values should be seperated by commas
    [Instead of other seperator symbols shown in rest of the options which are wrong.]

    Country of residense: INDIA

    Like

  17. The second statement is correct.

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

    Because, as you said group of values separate with a comma.And other statements have mistakes in syntax.

    Thanks for the tips.

    Adithya
    Country: India

    Like

  18. Answer is: second one
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    MySql Already have ths type of insert statement.

    Country : India

    Like

  19. HI Pinal,
    Correct Answer is Option 2:
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Thanks & Regards,
    Fazal Vahora

    Like

  20. Hi Pinal,
    Correct Answer is Option 2:
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Thanks & Regards,
    Fazal Vahora(India)

    Like

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

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

    This is working fine.

    Chennai, TamilNadu, India

    Like

  22. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  23. The second one – INSERT into tblSports values(1,’Football’), (2,’Cricket’) will work. Because of the row constructor feature in SQL. SQL needs to make only one lock to insert both these records.

    USA

    Like

  24. The Correct answer for the above question is

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

    Explanation : As this is the correct syntax for inserting multiple records also called as row constructors where in we insert multiple records separated by command but this option dosent work in SQL SERVER Express Edition.

    DILIP KUMAR JENA
    Country – INDIA

    Like

  25. Answer is 2

    INSERT into tblSports values(1,’Football’), (2,’Cricket’)
    will work as it has the correct syntax for Row constructor

    Abhishek Bhat
    India

    Like

  26. Hi Pinal!

    This is Ari from Chennai. Thanks for your wonderful effort. Happy reading now adays:)

    Quiz Answers:
    ——————-
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)
    Answer:2

    Thanks Pinal

    Regards,
    Arivazhagan Jaganathan
    Chennai | India

    Like

  27. 2) INSERT into tblSports values(1,’Football’), (2,’Cricket’)
    is the answer
    india
    –>
    * INSERT into tblSports value(1,’Football’,) (2,’Cricket’,) } missing comma
    * INSERT into tblSports values(1,’Football’), (2,’Cricket’) }correct
    * INSERT into tblSports values(1,’Football’) (2,’Cricket’) } missing comma
    * INSERT into tblSports values(1,’Football’):(2,’Cricket’) } : not required
    * INSERT into tblSports values(1,’Football’)::(2,’Cricket’)}:: not required

    Like

  28. The correct for this Question is option-2 ,
    i.e INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Thanks
    Narendra (India).

    Like

  29. Option 2 is correct. ie.,

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

    Vijay Daniel From Coimbatore, India

    Like

  30. Hi Pinal,

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

    Correct Answer:
    The second choice is the correct answer:
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    Explanation:
    Here row constructor values are correctly separated by a comma.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Like

  31. Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority

  32. Obviously second one
    INSERT into tblSports values(1,’Football’), (2,’Cricket’)

    But it is workign on sqlserver 2008 but not earlier versons

    Like

  33. 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

    Like

  34. 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

    Like

  35. 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

    Like

  36. Pingback: SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198) « SQL Server Journey with SQL Authority

  37. Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 | SQL Server Journey with SQL Authority

  38. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s