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
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 suggestion.
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.