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
what is the max rows can we insert into one table
What is SESQL
HI Pinal,
please help me out…i have a multiple insert query in a file which is about 125 MB (they are around 5 lac inserts). I tried to fire the osql command but it says that it cannot process more than 1000 inserts at a time…i cannot put GO lines after every 1000 inserts in that file…please help how to overcome this issue
Hi guys,
I like pinal site very much, but somebody posting waste answers, please don’t do like that. So please post only correct answers.
Better way:
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1), (‘Second’,2), (‘Third’,3), (‘Fourth’,4), (‘Fifth’,5);
Hi,
Every one of u r experts. Pls help me with sp syntax that will insert values into a table Named “Reference”
which has fields
ReferenceID int, ReferenceNo bignt, ReferencedBy varchar(50)
the sp will be called like
exec sp1(1,100)
it needs to insert 100 rows starting from 1 to 100 into the Reference field.
Waiting for your help
The basic code is
SELECT number FROM master..spt_values
where type=’p’ and number between 1 and 100
Also refer
Thanq for the above solution which was very useful to me.(About insertion of multiple values at single instance through query).
Hi All,
I am happy to get the code for inserting multiple records using one INSERT statement.
Special thanks goes to Pinal Dave.
cheers.
Hii,
Nice solution, thanks for sharing.
With Many Regards
hai,
i need insert multiple records table with clear example like table name as customer or employee etc for clarity im in starting stage
thanking you,
sreetheja.
Hi,
I am inserting multiple records in to a table through .Net application. With each insertion i have to increment the recid field with 1.
I tried,
insert into table2 (recid,…..)
select (max(recid) + 1), …. from table2
this is working for only 1record insertion. If I insert more than 1 record then the recid value is not getting incremented for each record I insert.
So, Can anyone give a solution for this???
create table product_master
(
product_no varchar2(6) primary key check(product_no like ‘P%’),
description varchar2(5) not null,
profit_percent number(2,2) not null,
unit_measure varchar2(10) not null,
qty_on_hand number(8) not null,
reorder_lvl number(8) not null,
sell_price number(8,2) not null check(sell_price>0),
cost_price number(8,2) not null check(cost_price>0));
insert into product_master values(‘&product_no’,’&description’,&profit_percent,’&unit_measure’,&qty_on_hand,&reorder_lvl,&sell_price,&cost_price);
ORA-01008: not all variables bound
can u plz explain why this error came and how to solve it
HI,
I just want to insert a new row in between 2 rows & also 2 news row in one single query
Eg.
Table A has 2 columns(no,name) & values are present in table as
1,’a’
2,’b’
3,’c’
4,’c’
so i want to insert a 2 new rows in one Statement
1. I want to insert a new row (5,’e’) in between 2 & 3.
2. I want to insert a 2 new rows (6,’f’ )& (7,’g’ ) at a time into a table at position above 1 & below 4.
also
Can i use where condition in Insert statement
can we do like this……….
Hi!
I am new to this and i need to create a table with 1,000,000 entries wherein it starts at 000000001 and so on. It has to be sequential. The second column must be composed of 3 random numbers from 000-999.
Does any of you have an idea on how i can achieve this?
Thanks a lot in advance. :)
i want to insert one table completely to another table
destination table is nk_np_pst
the inserting table is nk_np_unp
so wat command i want to give
Insert into nk_np_pst(column_list)
select column_list from nk_np_unp
Hi Shibu,
Assuming that both the tables have got same number of columns, you can write an sql like:
(a table to be inserted in b table)
SQL: Select a.* into b from a
Let me know if your number of columns vary (Table a number of columns is different to table b number of columns)
Hope it helps
Shreyas
Nice dude…that helped me save a lot of memory issues I was having with multiple inserts!!
i am extracting 15 columns from many tables which contains many sub queries and i am inserting into a table (using this select statements),but it takes 45 mins…
how to do this…
pls
This works too…
DECLARE @Target TABLE
(
[Id] int,
[Value] varchar(25),
[Size] int
);
DECLARE @Source TABLE
(
[Id] int,
[Value] varchar(25),
[Size] int
);
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (0, ‘Blue’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (1, ‘Orange’, 3)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (2, ‘Purple’, 2)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (3, ‘Red’, 2)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (4, ‘Yellow’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (5, ‘Black’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (6, ‘Green’, 4)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (7, ‘Brown’, 1)
INSERT INTO @Target ([Id], [Value], [Size])
SELECT [Id], [Value], [Size]
FROM @Source
WHERE [Size] = 1
SELECT * FROM @Target
I want to know is dere any method to insert multiple rows
By executing Insert command once and after that only entering values.
Thanks in advance