SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor

July 2, 2008 by pinaldave

I previously wrote article about SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL. I am glad that in SQL Server 2008 we have new feature which will make our life much more easier. We will be able to insert multiple rows in SQL with using only one SELECT statement.

Previous method 1:
USE YourDB GO INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('First',1); INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Second',2); INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Third',3); INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Fourth',4); INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Fifth',5); GO
Previous method 2:
USE YourDB GO INSERT INTO MyTable (FirstCol, SecondCol) SELECT 'First' ,1 UNION ALL SELECT 'Second' ,2 UNION ALL SELECT 'Third' ,3 UNION ALL SELECT 'Fourth' ,4 UNION ALL SELECT 'Fifth' ,5 GO
SQL Server 2008 Method of Row Construction:
USE YourDB GO INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('First',1), ('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 23 Comments

23 Responses

on July 2, 2008 at 6:25 pm Darshan shah

Hi ,
I want to update data into second server from first server ,without using linked server .
I want to use openrowset.
Can any one tell me how o do this ?
on July 2, 2008 at 7:26 pm Jerry Hung

Yah for another good 2008 feature, in addition to inline variable assignment
on July 3, 2008 at 9:21 am Khushal patel

SQL Server 2008 ROCKS!!!
on July 3, 2008 at 11:13 am Imran Mohammed

I always wanted SQL Server to accepts this kind of SQL Statements for INSERTS, finally they have come up with these…

Thanks again.
on July 3, 2008 at 9:29 pm drel

The syntax should be

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES
(‘First’,1),
(‘Second‘,2),
(‘Third‘,3),
(‘Fourth‘,4),
(‘Fifth‘,5)
on July 3, 2008 at 11:11 pm Scott

Wow! This is great. I cannot believe it’s taken this long for a feature that seems so natural.

Thanks for the sharing!

Scott
on July 4, 2008 at 10:30 pm Luke Phillips

Errr, drel, the syntax *is* exactly as you describe …!
on July 12, 2008 at 3:09 pm neearj

that’s good for new generation because after upgrade on sql 2005 that is new version as discuss mr. drel as example
……………..
so i appreciate to him.
haing a nice job.
on July 25, 2008 at 1:06 pm SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL Journey to SQL Authority with Pinal Dave

[...] Update: For SQL Server 2008 there is even better method of Row Construction, please read it here : SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor [...]
on August 20, 2008 at 12:35 pm Sujith Mysore

There is an easier way to do this.
If you are inserting a single row then use
insert into(a1,b1) values(1,2)

If inserting multiple rows using select statement use,

Insert into(a1,b1) (select a1,b1 from c)
on September 18, 2008 at 9:29 pm Deep

im new to tsql and your blog is of big help to me, more power.
on September 23, 2008 at 8:04 pm lakshmi

Hi,
Is below syntax works in sql server 2005 or it works only in sql server 2008?
iam getting some error when i try to use it in sql server-2005

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (’First’,1),
(’Second’,2),
(’Third’,3),
(’Fourth’,4),
(’Fifth’,5)
on November 11, 2008 at 5:14 pm gaurav

can we submit multiple listbox value into multiple table with the one insert query
on November 17, 2008 at 4:29 pm Nitin Reddy

@lakshmi: This is an MS SQL Server 2008 feature – you cannot use this with MS SQL Server 2005
on December 16, 2008 at 11:19 am rajasekar

how to retrive deleted rows in sql server(in oracle having FLASHBACK QUERY),i know only we have an consistant backup that only restore the data.
any other feature do u know pls tell me
thanks and regards
Rajasekar
on December 16, 2008 at 12:24 pm Tejas Shah

Hi Rajasekar,

You can not get deleted Rows in Sql server.

You can get it only in Trigger in “deleted table” or you can use “instead of trigger”

Tejas
on February 23, 2009 at 3:49 pm oracle

can you give us the function tha inserts loads of rows at one time! PLEASE :)
on April 15, 2009 at 7:13 pm Mauro

It´s a new feature of Sql 2008. Very Easy.
on June 3, 2009 at 6:03 pm Pederiko

I have a question about this new INSERT command in SQL Server 2008. Is it limited to only 1000 enteries? I have a lot more and I am getting the following error:
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
on June 17, 2009 at 3:29 pm rwallacej

Pederiko,
I have tried this out and am getting the same error – so must be a limitation
on June 26, 2009 at 1:01 pm Dhurendra

This is nice one but we can also insert more than one row as below:-

Insert into tableName(col1,col2) values(1,2),(11,22),(44,55);

Out Put
Col1 Col2
1 2
11 22
44 55

Thanks………
on July 4, 2009 at 10:48 am NaZAf

Hi,
There are other alternatives and better approaches than multiple statements shown here.

For example, a single INSERT query will not let you process the parameters before insertion, which makes this method weak. Using triggers will work, but will degrade your server performance.

So if your data needs no processing, you can use multiple insert statements. However, if you want to process them then I’d recommend using one of the following two new features since SQL Server 2005:

1. Using OPENXML
2. CLR User Defined Types

Just google them and you will find many examples and tutorials.
on July 6, 2009 at 11:47 pm al

Thank you for the brief article. It was brief and to the point.
I have used this syntax with other tools. It is ideal for loading initial definition data, and test data. Its simplicity is its strength.