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)
pinaldave
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 ?
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.
[...] 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 [...]
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)
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)
can we submit multiple listbox value into multiple table with the one insert query
@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 :)
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
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………
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.
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.
how do i insert the same record i a table by a number of times
@fred
Insert into table2 select * from table1
GO 1000
above statement will insert everything from table1 into table2 1000 times. Just change the number beside GO and and you are good.
This will work in SQL Server 2005/2008.
~ IM.
So can that list of values be passed in via parameter(s) of a stored procedure?
With sql server CE this query:
INSERT INTO Scontistica
(Prodotto,Sconto)
values (‘Sky’,0.5),
(‘Skywarm’,0.5)
give me this error:
[ Token line number = 2,Token line offset = 19,Token in error = , ]
why??
how we can insert a new row between two rows?
Hello Prashant,
As MS Excel, SQL Server doesn’t allow us to define the position of new row. But it can be done indirectly by creating clustered index. In a heap table we have no control to the physical storage location of a row.
Kind Regards,
Pinal Dave
hi,
You are doing great job, I really appreciate it. :)
I want to insert unique records in one insert statement. Is it possible? Thanks
Well, of course you can.
There was an article about setting IGNORE_DUP_KEY ON. Thats one way to do it. Distinct values are added to the table and duplicate values are ignored. Just remember to set IGNORE_DUP_KEY OFF when you’re done or you can get yourself into all kinds of troubles afterwards.
Other way, which I prefer more, is simply just to select only distinct values into the other table. For example, and this is very narrow example:
INSERT INTO TargetTable
SELECT DISTINCT * FROM SourceTable
Hello,
I am very new to SQL Server. I hope someone can help me. I have a large fixed width flat file that I need to import into an existing table in SQL Server 2008. I’ve tried using the wizard, but it either imports everything into the first column as one long string, or it defines the first column as 1 digit and imports everything into that column in multple rows. I was told that if I import the file as one long string, then I could write a statement that would allow me to import it into the table I’ve built in the proper (correct columns) format. I am not sure exactly which statement would be best for this. I will need to do this once or twice a week, so I only want to have to write the query once. I know this has to be possible, but I’m at a total loss.
Would it be better to use an update statement? For example
This is where I get confused. I am not sure of the correct syntax to use.
Use my_database
Update (my_table)
Set first_column varchar(4) not null = long_string_table.column_1;
Set second_column varchar(1) not null = long_string_table (do I need to say where each column begins and ends? That seems to make more sense.)
Any help is appreciated. Thank you.
thanks for the help
Thanks a lot sir.
when i use the query the
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5)
in sql server 2005 it is giving error
Incorrect syntax near ‘,’. ca just tell me it will work with 2005 also ? or it will work with only 2008
@Sabir
It won’t work in SQL Server 2005 and below version.
It only works in SQL Server 2008 version.
~ IM.