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

Feed on: Posts; Comments

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://www.SQLAuthority.com)

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

16 Responses to “SQL SERVER - 2008 - Insert Multiple Records Using One Insert Statement - Use of Row Constructor”

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