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 Pinal
If you have time, can you help me please?
I want to Copy values from one table to another and sum some value at the same time. this is my sentences and works:
INSERT INTO carga ( Item, REC_Rep, Invoices, Description, Qty, Skids, Boxes, Bins, Metal_Rack, Totes, Other, tqty, tskids, tboxes, tbins, tmetal, ttotes, tother) SELECT DISTINCT Item, REC_Rep, Invoices, Description, Qty, Skids, Boxes, Bins, Metal_Rack, Totes, Other, SUM(Qty) AS Qty, SUM(Skids) AS Skids, SUM(Boxes) AS Boxes, SUM(Bins) AS Bins, SUM(Metal_Rack) AS Metal, SUM(Totes) AS Totes, SUM(Other) AS Other FROM tmp_carga
but I only got one register from the table tmp_carga.
How can I got all the data from the table tmp_carga and insert them into the table carga and Sum some fields at the same time?
Thank for your help!
SIR i am making an application for the small organisation in which i encounter a problem..
Kindly help me by reading the following doubts that is :
i want to break an amount into smaller one for an example:
if i enter rs 200 from the front end it breaks into the 20 rows with the number leaving rs 10 in each field ..
please help me my application stucks in between …….
thank you
G.vinay
Please help me, I am new to VB.Net and Mysql but I would like to learn. I have a table called “test” with 3 columns “id” “name”, “surname”
How can i add 1 name and 2 surnames as 1 record .eg a person has name Ken and sunames Fig & Smart
table1 has one record
table2 has one record
these record are not same but field name is same
when these two tables are union, only one record is received.
please help me
Tin Tin – please provide sample DDL, data and expected output.
one table for example marksheet use id = pk and rollno use table insert data roll no is same no are display in dtabase .how to solved that rollno is not same enter to insert time
How can I insert more than 1000 rows in a temp table
Create table #temp (i int)
go
insert into #temp (1)
go 1000
sir I am stuck in a logic ..
I have dynamic tables . lets say one large data Big_Table is stamped with profile 1,2,3,….n
so i create table profile1,profile2,… profilen (example profile1 :- select * from Big_Table where rule = profile1)
table data example :-
table_profile1
transaction_id profile priority
1 1 1
2 1 1
table_profile2
transaction_id profile priority
1 2 2
3 2 1
now lets say we got single transaction in Big table is repeated in table_profile1 and table_profile2
so i need to pick all data of table_profile1,table_profile2,table_profilen with lower Priority
so here in target transaction_id 2 is repeated in profile1 and profile2 table so final table will have only transaction id 2 with lower priority of profile 1 as its 1
Big_table_stamped
transaction_id profile priority
1 1 1
2 1 1
3 2 1
so this final stamped table Big_table_stamped
query i am dynamically creating is
select transaction_id,profile,priority
from (
select row_number() over (partition by a.transaction_id order by a.priority asc) r
a.*
from (
select * from table_profile1
union all
select * from table_profile2
union all
.
union all
select * from table_profilen
) a
) data
where data.r=1
)
but this is slow … i may have 600 tables too..
sholud i first put all data in one table ..
we broke tables to make faster process
should i join all tables .
or what should be my approach
I dont have much time
how to insert more than 500 records into table without duplicates
There seems to be no need to use either extra insert statements or union clauses. Why do you prefer the use of union?
My way to insert multiple records would look like this:
insert into TESTNILS (ZAHL, TEXT) values (1,’eins’), (2,’zwei’), (3, ‘drei’), ….
Nils
When this article was written at that time the new syntax which you suggested was available in SQL Server.
I hope this helps.
Hi Pinal,
I just saw your tweet with the link to this post. To make it more valuable, I would suggest adding this example:
INSERT INTO MyTable (FirstCol, SecondCol) VALUES (‘First’,1), (‘Second’,2), (‘Third’,3);
Regards,
Goran
Great one
Suppose I have a list of comma separated numbers
123,
345,
888,
999
I want to insert them in a temp table, how would I be able to do this?
Sir, What to do in case if I want to insert IMAGE also (as byte array) ? How can I use the same syntax ? Is there any idea?
Can we use xml for multiple insert?
Thankyou, It is great suggestion.