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 need a single storedprocedure tht perfrom a single functionality like insert for diferent forms, depending on which from is been used ,the record needs to be inserted in its respective table.
Please help me in this issue
thanks
Do let me know solution for my query .
Hello Rajni,
You explained that when same row is inserted more than one time that identity values increses and that is the only difference between two rows. But what is your query about it?
Regards,
Pinal Dave
i am not able to get why the entire row is inserted for 2 times .whereas there should be single row entry.
Run a profiler and see if you get an idea on why it is inserted twice
I have query with union statements but it is comparitively very slow. Is there any alternative method instead of union or union all that can be used to increase the performance. If so can you give me some sample query.
Hello Rathy,
To append two tables, union all is an optimized clause but you can test insert into another table as an alternative. Like
SELECT * INTO temp FROM Table1
INSERT INTO temp SELECT * FROM Table2
SELECT DISTINCT * FROM temp –alternative of union
SELECT * FROM temp –alternative of union all
In SQL Server 2008 a new alternative is MERGE clause but that would work faster only if these table have identity fields.
Regards,
Pinal Dave
hi,i want a stored procedure to insert multiple rows at a time. can any one help me
@Rathy
Are you sure it is the SELECT statement(s) that is slow and not the INSERT statement? Run SELECT independently to see if that’s the problem.
What is the problem with this besides it’s slow? Do you need to run it frequently for some reason?
@chinni
Just create a new procedure like you create any other procedure:
CREATE PROCEDURE InsertMultipleRowsAtOnce
AS
BEGIN
SET NOCOUNT ON;
— Your INSERT .. SELECT statements goes here
END
Hello Chinni,
There is a lot of information about stored procedure in BOL and on msdn.
If you are facing some issue in writing stored procedure for some specific case then let us know the details.
Regards,
Pinal Dave
I am fetching some columns from some specific tables using a select statement. One or two conditions are distinct for fetching the columns all the other conditions are same. So I am using union all statement. Is there any alternative way for doing the same instead of UNION or UNION ALL.
Thanks,
Rathy.
Hello Rathy,
If you can provide the queries we can help you in writing a single query instead of multiple queries.
Regards,
Pinal Dave
This WORKS on Access!!!!
INSERT INTO LC__TaskAssociation (lngCheckID,lngTaskNumber)
SELECT Table1.lngCheckID, Table1.lngTaskNumber FROM (SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID) As Table1
hi pinal
i have to match two tables and insert the matched data in a separate table and unmatched in another table what should i do . please help
aruj
Hi Aruj,
To get matched records use INTERSECT clause and to get non-matched records use EXCEPT clause. Write two SQL statements to perform these two operations and insert the results in target tables.
Regards,
Pinal Dave
Hi pinal,
I attended a interview on Saturday(6-2-10) .He asked a question that
Can we use function to insert values into Database?
I answered no … because i think function are used to return values….
Does my answer was right ??? I am beginner to mssql
Thanks in advance
Chethan.K.V
Hello Chetan,
Your answer is correct. Any type of changes can not be performed through function.
Best of luck!!!
Regards,
Pinal Dave
to diffrant database but the table are same one table to onther table record are append(1598 record ) how to append the data onther table
By append, to you mean INSERT from one TABLE to another, or to get the results from both TABLEs?
hai if i am leaving the values for some fields in the insertion query it should not display the error it should take the value and store in the datbase as ‘zero’.Thanks in advance .Plz reply mesoon
hai if i am leaving the values for some fields in the insertion query it should not display the error it should take the value and store in the datbase as ‘zero’.Thanks in advance .Plz reply mesoon
You should make those columns to have default value 0
Do these COLUMNs have a DEFAULT? A DEFAULT value is supplied when the statement doesn’t INSERT it. That DEFAULT value is NULL, which will cause an error if the COLUMN is set as NOT NULL.
hi dev,
this is babu.i am new to diz blog.
here i have a problem .regarding insertion of multiple recards at a time in temp table.
as you see below i declared one temp table
then i execute my query and inserted into temp table.
but only last record in inserting here when i did like diz.
plz any help is appriciatable .
(i have to take whole records in diz temp table so further i can use like operater on it to filter)
create table #temp(post varchar(100),[user_id] int,[image] varchar(100),city_id int)
declare @a varchar(100),@b int,@c varchar(100),@d int
insert #temp values(
select distinct a.post,a.user_Id,a.Image,a.city_Id from ads a,cities c,items i
where a.item_id=i.item_id and a.city_id=1)
select * from #temp
Your code should be
create table #temp(post varchar(100),[user_id] int,[image] varchar(100),city_id int)
declare @a varchar(100),@b int,@c varchar(100),@d int
insert #temp
select distinct a.post,a.user_Id,a.Image,a.city_Id from ads a,cities c,items i
where a.item_id=i.item_id and a.city_id=1
select * from #temp
How many records does the SELECT actually return?
Hello Babu,
Rewrite your query as below:
insert into #temp
select distinct a.post,a.user_Id,a.Image,a.city_Id
from ads a,cities c,items i
where a.item_id=i.item_id and a.city_id=1
Regards,
Pinal Dave
Hi,
I have 2 queries which retreive same column with different values. I want to add the values of these two columns(corresponding values) using these 2 queries. Any kind of help will be highly appreciated.
Regards,
Neha