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.

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

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)