Update: In SQL Server 2008 there is an even better method of Row Construction for inserting multiple records, please read it here: SQL SERVER Insert Multiple Records Using One Insert Statement – Use of Row Constructor
This is a very interesting question I have received from new development. How can I insert multiple values in a table using only one insert? Now this is an interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.
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
The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… Clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps my focus on the task, instead of copy paste. I have explained following script to new developers. He was quite pleased.
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
The effective result is same.
Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Union vs. Union All – Which is better for performance?
876 Comments. Leave new
I try to execute 400 insert statements to table about 20 columns (int or float). Regarding performance there is MUCH difference. ;)
INSERT statements way: about 5 seconds
INSERT SELECT UNION ALL way: 40 miliseconds!!!
thats funny isn’t it?
thanks a lot for this trick.
INSERT using UNION is not a way to improve the performance. I had tried inserting 35706 records to a table (int values).
The normal insert way (35706 insert statements) took just 9 seconds to complete the execution whereas the union all way takes more than 3 minutes.
I did not continue after 3 minutes…. :)
union ALL
I have a 200 rows to insert and I tried to use your way because it seemed much faster, but since I didn’t know how to end it properly, it generated an error.
that’s what I wrote:
INSERT INTO FACULTY (faculty_id, fac_name)
SELECT ‘F123’, ‘Jim Taylor’
UNION ALL
SELECT ‘F124’, ‘Amy Johnson’
UNION ALL;
Can you tell me what did I miss?
Thanks
everything is fine even i did the same thing but getting result…
Hi,
You miss go command at the end of insert.
Try u will not get error.
Thanks,
siva
remove the last union all
I am sorry i forgot to say i have not inlcluded last UNION ALL
Remove the last Union all.
Hi Ruba,
There is not a big problem with your code .
You just had an extra UNIONALL after your last Select statement.
Thanks,
Vipin Teotia
Remove the last “UNION ALL”.
If you look at the example, there’s no “UNION ALL” after the final entry. I haven’t tried it but this is my guess as to why you might be getting errors.
Hello pinaldave,
Myself Gurmeet singh working as Soft. Engg in Mohali.
I have read ur blog its really helpful.
Wish u very good luck.
Hello pinaldave,
Thanks a lot to provide such hidden info about sql server
I never thought that we can do this with a single statement.
it’s really helpful us.
wish u all the best
Hi,
Thanx, it was very use full for me.
You have solved my big problem.
Thnx again.
RandhirSingh
Data Base Developer
Haryana(India)
This solution is useful, but it does not appear to scale well. For 1000 records things went swimmingly. I then tested with 5000 records and an error was returned:
“Internal Query Processor Error: The query processor ran out of stack space during query optimization.”
Just thought I would provide a bit of warning. This does appear to perform better than a single command with multiple INSERT statements, but the cost in server resources may be a problem. Has anyone else experienced this?
In that case, have the data in a file and import it using BCP or BULK INSERT
If you need to insert multiple records at a time then you may use a simpler syntax. Here is how it goes:
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘First’ , 1) , (‘Second’ , 2) , (‘Third’ , ‘3’), (‘Fourth’ , ‘4’) (‘and so on’) ;
This is what I use. It is simple and effective.
However, I wanted to know if there is any way to upload values in a batch from text file with values to a SQL database.
This syntax is supported from version 2008 onwards
Also if you have data in a text file, you can use BULK INSERT
Refer this
Thank you sir.
I have used this query to insert multiple record.
There is still a question.
If there are lac of record insert in same query. will there any problem with the query.
or we have to split the query with thousands.
Thanks in advance.
In that case, have data in a text file and use bcp or bulk insert
INSERT INTO checkmultipleinsert(id , name)
VALUES (1 , ‘A’) , (2 , ‘B’) , (3 ,’C’);
this query genrate an error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
I have sql2008 pls reply
@Deepak
it works in the sql2008 server..
Sumeet, the syntax you provide is only valid on mysql, not sqlserver2005. i believe pinal’s method is the only one that really works (barring the aforementioned caveats, of course).
Hi It’s helpful
But is thr someway where i can use a single insert to insert values for some columns from one table and the remaining columns from another table?
Sri
i wanna insert more number of column records using select sub query.
Post some sample data and explain how you want to import the data to a table
Thanks
Say if i don’t have a common field to join then what can be done?Coukd you pls tell me?
Post some sample data and expected result
Pinal,
Good to see your site & blog.
You could generate a script which does that, if the values are stored in some tabular format
e.g. if you have an excel worksheet containing rows with data – you could write a formula in an excel cell (for each row)
OR
you could write sql statement do generate insert statements
e.g. select “insert into mytable (field1, field2, field3) values” + field1 + “,’ + field2 + “,” + field3 “)” from mysampletable
No need to do copy-paste :)
Sir,
I want to know that how i can update one by taking data from other table.
For Exmp. There is one master table having col name ID, Amt.
There is one second table daily account( Id, amt_dipo). How i can update the master table in the evening
With Best Regards,
Somesh Vashisht
You may need to use
update m
set amt=amt+d.amt_dipo
from master as m inner join daily_account as d
on m.id=d.id
Hi,
This blog is very good and helpful. I need to insert records into 2 tables at a time. Can you please tell me the procedure for it.
Thanks & Regards
Hari
You need to use two insert statements
u can do two insert statements or
use trigger ok
In response to question 9:
Try ‘Bulk Insert’ to load data from a file.
Hello. Thank you for creating a website like this. It is very helpful.
I have a question. Is it possible for an INSERT command to fail?
In what situations would this happen?
I insert about 1000 records in a table and only the first 200 get inserted. The rest of the other 800 do not. I split the process by inserting in 5 batches, 200 records each and all are successfully inserted? What could be the reasons for an incident like this?
Thank you for any help you can provide.
there is one cause for it is connection break up. you can set the connection time greater. Try it..
This blog is very helpful. I got a lots of things new here. keep it up.
Thanks.
can you please suggest an answer for my question
how can we insert the values into different tables at a time
Hi,
thank you for your suggestion,can you give me an idea of how to insert data which is in the form of xml into a table.kindly explain with an example.
ragards,pavanich
Hi,
you have given suggetion for inserting multiple rows at a time in a table using query.but whats the difference between inserting the data using insert into query and the one you have given.even the one you have suggested is very big.i mean to say here also we use many statements.
Regards,
pavanich.
Hello ,
Really this is very useful site for me too…