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.
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 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)
117 Comments. Leave new
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
The right answer is #2:
Insert into tblSports values(1,’Football’), (2,’Cricket’)
Francisco,
Miami
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
Answer is #2:
INSERT into tblSports values(1,’Football’), (2,’Cricket’)
Thanks,
Wayne
The correct answer is;
INSERT into tblSports values(1,’Football’), (2,’Cricket’)
Bulent
answer is # 2:
I am from USA
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
INSERT into tblSports values(1,’Football’), (2,’Cricket’)
Option :2
INSERT into tblSports values(1,’Football’), (2,’Cricket’)
SERT into tblSports values(1,’Football’), (2,’Cricket’)
option 2
INSERT into tblSports values(1,’Football’), (2,’Cricket’)
INSERT into tblSports values(1,’Football’), (2,’Cricket’)
this is right answer
thanks
Asmita
from india