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
Hi all,
How to load bulk data in db2 database??
The database resides on OS/390 machine.
i want to load data in tableA(say) from other tableB(say0 which is in other DB2.. ..
Both the tables are same??? but resides on different DB2..
can anyone help on this???
I tried using export and import commands. but it takes a hell lot of time….
is there a way to load the data faster than this??………..
Hi Dave,
Presently I’m using in sql server below mentioned query for insert in a stored procedure. I have declared the variables @a, @b and @c respectively. Is this the correct way?
If I do it without variables I’m getting correct output whereas with the variables I’m getting duplicates. Can anyone please help?
Also the stored procedure returs me correct records as well as null records without variables. Is there any way of getting rid of NULL? I’m using cursor.
insert into
(select @a = a, @b = b, @c = c from a,b,c where a = b and b = c group by… order by…)
Thanks
Shreyas
I have a similar query to one of the above. Perhaps you could help.
I have 2 tables Login and Salt
I want to insert UserName,Password into the Login Table and
instert LoginID from the Login Table and Salt into the salt table.
Please help
Regards
Simon
The method using …. UNION ALL … brought me a slowdown of factor five. Seems it highly depends on the amount of data.
How do you remove orphan records from a table which was not referenced by a foreign key?
Thanks,
Liz
Most likely it should be
Delete from child_table
where col not in
(select col from parent_table)
I have two database with same tables. Depending on a column value(takes binary value 0 or 1) in one table I have to copy those into another table if that column is 1.
how can i do this?
thanks in advance
Ravi D
very good article i wanted to know that can we practice
administration part articles sitting on the local machine
because i tried to apply permissions to the user but was unable to do it
Thanks and Regards,
Satnam Singh
Database Developer
MUMBAI
very good article.
i tried , its working fine.
Thanks and Regards,
shishma
S/w engg
Blore
Hello,
I need help to insert multiple rows from one table to another.I have to do something like this.Select the one colum from the table and rest all i have to insert by myself.How do i do i?
It should be something like
Insert inot target_table(column_list)
select col, your_value1,your_value2,….
I USE SQLSERVER.IN MY PROJECT I IMPORT A DATABASE WITH MAXIMUM 20000 RECORD IN A DATA BASE WITH 200000 RECORD.IN THIS PROJECT WE COMPARE THIS IMPORTED RECORD WITH SECOND DATA BASE THAT NEED SOME INSERTING AND SOME UPDATING IN LARG SCALE DATABASE.I WORK WITH DATA SET AND I MUST COMPARE AND MANUPLIATE DATA IN FIELDS !! BUT THIS WORK GET ALOT OF TIME FOR UPDATE AND INSERT !! HOW CAN I SAVE TIME AND DO WORK FASTER.
Hi..
I need to insert a clob type data into a table.
I break this clobe into few chunks and added them into a temporary table. Now i want to add those chunks into a one column of some other table.
it is not allowed to use text type data locally with in a stored procedure? how iam suppose to do this?
Thanks
Sandevni
Hi Sql junkies,
I have a problem with scheduled backups. Any backups run through database maintenance plan or called by procedure xp_sqlmaint hang. They just show status as executing but nothing happens. I ran a profiler too and checked log messages, no clue. Nothing has been recorded in log, etc.
Please can somebody help me urgently?
Many thanks
Shreyas
Pinal,
I m Hitesh Agja and I have prolem with multiple insertion. My table has 10 fields including one identity field. and i want to insert more then 10,000 rows in just one shot…how can i do this…union all gives me “Insufficient system memory” kind of error. Plz help me out budy….i m trying with bulk insertion what are the other options for me pinal..
Hitesh Agja
(Ahmedabad)
Where do you have the source data?
If you want to insert 10000 rows in one shot, better have it in a text file and import to table using BULK INSERT
I got the solution…faster then ever you have seen….bye guys….
hi…
pinal if for multiple record insertion i do not have fix number of records then how i can insert multiple insert using your union all type query………..actually i have tried and find error…bcoz …i have not fix record insertion
Hi Pinal,
I am using your way to insert bulk data. But i am facing a problem in this command.
if there are 100 records to insert if any of statement raises error, no record is insert into database table.
can i ignore these type of error and insert error free data.
Actaully i am read data from excel sheet and insert into sql database. there is problem of primary key constraints. i have made user define id and user id composite key.
so how could i achieve this thing
Since it is now a single INSERT statement, it is atomic (it will all succeed or fail). You could check the potential INSERTs ahead of this, but that may be as much trouble as performing each INSERT individually.
Super reference site – thank you for having this – very helpful!
I need to update many similar db’s with a similar record in one table – and most of the record is populated with my known text, but a few fields need to be carried over from another row (easily identified) in that same table (the value is obviously different in each db) – but in the same table. Can this be done with one statement or do I need to run an Insert followed by an Update statement? For example…
===
INSERT INTO TableXYZ
(Col1, Col2, Col3)
VALUES (‘Value 1’, ‘Value 2’, ‘Value 3′)
===
UPDATE TableXYZ
SET Col4=’Value 4′, Col5=’Value 5’
Super reference site – thank you for having this – very helpful!
I need to update many similar db’s with a similar record in one table – and most of the record is populated with my known text, but a few fields need to be carried over from another row (easily identified) in that same table (the value is obviously different in each db) – but in the same table. I want to run it in one statement (repeat it for each db), but will use two statements if needed. I can’t seem to get the Update statement to work though. Can you help???
For example…
===
INSERT INTO TableXYZ
(Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)
===
UPDATE TableXYZ
SET Col4= a, Col5 = b, Col6 = c
WHERE (Col1 = ‘Value 1’) AND EXISTS
(SELECT Col4 AS a, Col5 AS b, Col6 AS c
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’)
Hoping you can assist – thank you!
Tim
Here’s what I figured out works if I use two steps:
(Step 1: Create a new record with known data in TableXYZ)
INSERT INTO TableXYZ (Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)
(Step 2: Update that new record by referencing data already existing in the table (ie: Col1 = ‘Value 1’)
UPDATE TableXYZ SET
Col4 = (SELECT Col4
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’), Col5= (SELECT Col5
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’), Col6 = (SELECT Col6
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’)
WHERE (Col1 = ‘Value 1’)
– Thanks again, Tim
hi
can we insert multiple record at the same time in sql + ?
please help me..