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
hai i using stored procedure to insert records in to the database table taking only 99999 records not morethan that please tell me where i have to do changes
Thanks in advance
Hi Pinal
This was realy an informative forum for me. I want to ask one question if u can answer that what is the replacement of [Sum(column) over(partition by column order by column) as Alias]
in sql server 2000. This function is supported in sql server 2005,2008 and in oracle.
Regards
Ijaz, Pakistan
SELECT column , sum(some_col) from your_table
group by column
order by column
hi,
i am new to sql server 2000
I have a table W_ORDER_F with 64 coloumns and 532 rows . i need to create a backup table W_ORDER_FBKUP.
can you healp me to get the syntax?
with regards
Rani
This is one method
SELECT * INTO W_ORDER_FBKUP FROM W_ORDER_F
Hi !!
I created Unicode DB using sql 2005 and insert value using this statement,
Insert into Test1(Name,Address ) values(‘vasana’ ,N ‘Ήεàĺћ & §àεý’ );
This work fine,
The problem is with my select statement,
select Name,Address from Test1;
it give result like this
vasana , ???????
Can u please tell me how to write proper select statement to get Unicode value .
Thanks.
hi,
java and sql query for getting primary key of recently inserted row
Hi,
which one better ‘OR’ or ‘IN’ to filter a result set
Hi Pinal,
I have 2 tables as below :
1. mst_Customer
2. mst_Installment
mst_Customer contains customer infor with unique id which is tagged with mst_Installment.
I would like to retrive only 1 record from mst_installment on the basis certain criteria like date etc. and would like to show against mst_customer table record. Query I am ready with return proper record if I use top 1 but when it comes to more than that result is not proper at all..
Hi Pinal,
I accidentally browsed ur blog.Its very informative.keep up the good work..when i followed ur same techinque and got this error,can u pl help. iam new to this sql concepts
SQL> insert into employee_usa(e_id,e_name)select 100,’pranu’ union all select 101,’satyam’;
insert into employee_usa(e_id,e_name)select 100,’pranu’ union all select 101,’satyam’
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
For ORACLE you need to use FROM DUAL
insert into employee_usa(e_id,e_name)
select 100,'pranu' FROM DUAL
union all
select 101,'satyam' FROM DUAL;
union all
select 100,'pranu' FROM DUAL
union all
select 101,'satyam' FROM DUAL
Hi Pinal,
I am trying to insert data from one table to another using a procedure,but its erroring out.
Insert into table2 select * from table1 where col1=@var
Error:Incorrect syntax near Insert statement
Regards
Babu
@Babu,
Script what you posted seems to be incomplete, and may be error is in the script which you did not posted here… I dont see anything wrong in your script you posted. Just check the line above insert statement, make sure you declared @var variable…
here is an example…
CREATE TABLE table1 ( STATUS VARCHAR(50))
go
INSERT INTO table1 (STATUS) SELECT ‘GREEN’
go
CREATE TABLE table2 ( STATUS2 VARCHAR(100))
go
DECLARE @var varchar(100)
SET @var = ‘GREEN’
INSERT INTO table2 SELECT * FROM table1 WHERE STATUS = @VAR
go
select * from table2
go
drop table table1, table2
works perfectly fine with me…
Regards,
IM
Hi Pinal,
Good to see your website which gave a lot of solutions.
And I have a question..
Why we go for table variables rather than cursors and views?
Can you please clearify?
Thanks
Regards Prakash
Hi Praveena,
insert into employee_usa(e_id,e_name)
select 100,’pranu’
union all select 101,’satyam’
Its working in sql server 2005..
Which DataBase are you using?
Regards Prakash
Hi Durai..
I just went to this site today..and cheked your comment..
How to retrive 7th record in sql server 2005 with out using field condition like( where Empid=7). Is there possible to retrive using rownum=7 in sql server 2005
so u can do like this
select * from Table where ID=
(SELECT MAX (ID) FROM Table WHERE ID in
(SELECT top 7 ID FROM Table ORDER BY ID ))
This is not only for ID ,from any column name u can get the corresponding row..
Regards Prakash
Hi Durai,
Even better way for that…
select top 1 * from Table where ID not in (select top 6 ID from Table)
Methods 5 and 6 may be better
Refer this
Hi All,
I want to get max id from that table where i am inserting data by ‘Select’ statement.
I have follwing T-SQL and Function:-
1. T-SQL:::::
INSERT INTO Risk([Description],RiskCode)
SELECT distinct(Risk),dbo.ReturnUniqueRiskID(Risk)
from ImportRiskControlErrorLog
2. Function:::::::
ALTER FUNCTION [dbo].[ReturnUniqueRiskID]
(
@Risk VARCHAR(50)
)
RETURNS VARCHAR(50)
BEGIN
Declare @UniqueRiskID VARCHAR(50);
SELECT @UniqueRiskID=’R-‘ + CAST(MAX(CAST(SUBSTRING(riskCode,3,9) AS INT)) + 1 AS VARCHAR(50)) FROM Risk
WHERE RISKCODE LIKE ‘R-%’
IF @UniqueRiskID IS NULL
Set @UniqueRiskID=NULL;
Return(@UniqueRiskID);
END
Regard
Kris
You are really greate Dave. The article was posted nearly 1 and half year back. Still there are comments running on this article. This is the first time I have ever seen. Keep it up Dave.
Hi Pinal,
Can you please suggest your favorite method from above article when you have 50 insert statements(4 fields to update) with each field’s value not exceeding 100 char – there may be 50 concurrent users ?
Many Thanks,
Rumba.
Hi all,
can you please send me coding for the insert the values from different forms. my coding capture the 2nd form values only. this is not capture the first form. so please help me
thanks
This is a good suggestion it does not perform well when inserting lots of records (20K in my case).
I did a comparison between the Method above (using Union All) and using a stored procedure.
I’m trying to insert:
total columns in table: 2
columns to insert: 2
rows sample: 20,000 rows
data types: int
table status: empty before insertion
Method one:
—————
using stored procedure:
basically the sp takes two parameters, one for each column, and it will do a straight insert into the table as in:
————————————————–
Create PROCEDURE [dbo].[sp_record_Insert]
@id1 int,
@id2 int
AS
SET NOCOUNT ON
insert into myTable(id1,id2) values(@id1,@id2)
————————————————–
and the calling code is like:
————————————————–
exec sp_record_Insert 140319,47857539
————————————————–
this is executed 20,000 times to insert 20,000 records
TOTAL Execution time: 3 seconds
Method 2:
————
Using the Union All method took: 5 minutes and 9 seconds.
So I beleive you can use the union all method on smaller number of records, but once you go beyond that, you are better off trying something else.
Hi Folks, wanted to report my real-world test on this one.
We have a database operation doing 2.5MM row data loads on a daily basis – so performance is of interest.
We ran a test with the 3 different syntaxes from above:
1) many individual inserts, one per row
2) one multiple-row Insert, one total for all rows
3) Union All
Loading into a 9 column table with a couple of indexes on it, we found the following:
1) 00:03 – Even with the extra overhead of the command, this is by far the fastest for us.
2) 00:10 – A little slower.
3) 01:30 – The slowest of the bunch. Maybe better for smaller sets?
We ran on Windows Server 2008, with SQL 2008. This is a quad-core with Intel 2.83GHZ cpus, 8GB RAM, and a 7200 RPM data-drive.