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
@Ravikiran,
You mentioned one column name as “Source_Syatem” , is it correct, I think it should be Source System.
Second thing, if everything is correct, then may be the datetime values you are passing are incorrect, check the format how table is configured.
a) check if the date column has datetime datatype or character datatype.
b) if it is character then you need to change the values you are passing.
c) if it is datetime then check the formatt ( style of date).
Also check the datatype of “price” field, it is suppose to be decimal ( X.2) – where X can be any value.
Hope this helps
Thanks.
I am trying to insert above 5000 records in a database using the “select” and “union all” statements. i am getting error “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
It really helps to insert multiple values in a single table.
Following is the one way by which we can insert the value:-
DECLARE @Authors TABLE(lastname VARCHAR(20), firstname VARCHAR(20))
INSERT Authors VALUES (‘asd’,’asd)
INSERT Authors VALUES (‘gfh’,’fgh’)
……
…..
…..
SELECT * FROM @Authors
GO
Hello pinaldave,
Myself Madhusudan Pawar working as Soft. Engg in Mumbai.
I have read ur blog its really helpful.
Wish u very good luck.
Thanks
hi Pinal,
I read this articles. very nice. Can u tell me some more.
Actually i used one stored procedure for inserting multiple values from asp.net. The number of rows inserting is according to the user requirement. So i create a command object with the stored procedure name with parameters and put one loop statement for inserting the values. here u provide one method for multiple insertion but i think this method is static ie if i put 5 rows i can use 4 union all like that. But i cant assure the number of rows. so how i can do with one insert query or stored procedure.
Dear Pinal Dave
I have a field named reminder as datetime in database.
I need to check whether today’s date and month are equal to the reminder date and month. don’t consider year.
So how this can be compared.
Please Help
Regards
Deepak Biradar
One method is
select columns from your_table
where day(date_col)=day(getdate()) and month(date_col)=month(getdate()
how can i isert more than one rows in a same field, one of the field is set as primary key. inserting each row is not possible . please give a solution.
Hi Everybody….i want to write sql script which will insert 1000 data in a table..and i need to complete the script by 2morow morning..i need to auto increment the number i.e..422000001199,the last 4 number needs to be changed..any idea how to proceed plz..thanks in advance
Why don’t you use newid() which will generate auto ids for you.
select newid(), contactname
from Customers
I have used northwind database.
i want the syntax of inserting multiple rows with
insert into tablename values(‘&col name”);
Hi!
Good work guys …
I tried to migrate a table from MySQL into MSSQL 2000 using the same technique mentioned in this website using multiple SELECT and Union Alls .. but the trouble is it works fine on SQL Server 2005 Express till about 2000 records … but in SQL Server 2000 I have been facing:
“The query processor ran out of stack space during query optimization” error …
So, be carefull if you are running this query on SQL Server 2000… Due to 8k limit of Row Data in SQL Server 2000 and SQL Server 7.0.
Cheers
Aleem Latif
————–
Sir,
i want to insert records in a table but in ascending order by name or ID.
how to do that.
regards,
Anirudh Sood
Programmer
Storing Ordered data doesn’t matter
You should explicitely use Order by clause in the SELECT statement
Oddly, this approach was way slower for me.
hi Thanks the solution. but i need to insert 500 records at a time and inserting for different values every time is difficult.. plz can anyone help me on this.
this is very useful to new developers such as me, very thanks to the author.
thank you! you saved me :)
i have a problem
all of my data is stored in a text file, each column seperated by a comma, and each record in a new line
how can i insert this data in an oracle sql table
This insert into -select works great, thanks very much
Hello sir,
how to insert the records for multiple tables using one form