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 (https://blog.sqlauthority.com)
120 Comments. Leave new
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 ?
Read about OPENROWSET in SQL Server help file
It has example on how to communicate with different server
Yah for another good 2008 feature, in addition to inline variable assignment
SQL Server 2008 ROCKS!!!
I always wanted SQL Server to accepts this kind of SQL Statements for INSERTS, finally they have come up with these…
Thanks again.
The syntax should be
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES
(‘First’,1),
(‘Second‘,2),
(‘Third‘,3),
(‘Fourth‘,4),
(‘Fifth‘,5)
Wow! This is great. I cannot believe it’s taken this long for a feature that seems so natural.
Thanks for the sharing!
Scott
Errr, drel, the syntax *is* exactly as you describe …!
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.
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)
It should be
Insert into(a1,b1)
select a1,b1 from c
Excellent!
im new to tsql and your blog is of big help to me, more power.
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)
This will work from version 2008 onwards only
This will work from version 2008 onwards only
I tried it in SQL server 2008R2. It is not working. It is giving “Invalid column Name ‘firstcol’ “” error
have you created table? use below
Create table MyTable (FirstCol varchar(100), secondCol int);
go
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5);
go
select * from MyTable;
can we submit multiple listbox value into multiple table with the one insert query
No. You need to use insert statement for each table
@lakshmi: This is an MS SQL Server 2008 feature – you cannot use this with MS SQL Server 2005
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
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
can you give us the function tha inserts loads of rows at one time! PLEASE :)
Can you give use some informations on what you are actually trying to do?
It´s a new feature of Sql 2008. Very Easy.
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
Yes. It is limited to 1000 entries. If you want to insert more than 1000 rows, move data to text file and use bulkinsert
I ran into this too and find this arbitrary value of 1000 to cause me to scratch my head. Did they roll dice to come up with this value or something? You shouldn’t have to put data in a file and use BCP to import say 1001 records. I know other databases that I have used this syntax with and it worked just fine with well over 1 million values with no issue.
Pederiko,
I have tried this out and am getting the same error – so must be a limitation
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………