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,
i have table with columns uniqueid, datetime, float, smallint. In my test code i’m creating collection of dummy objects (with related properties) and then i’m inserting them to my table.
1. one by one by calling stored procedure
2. insert with multiple select
3. multi insert
results are following :
for 1200 records are time like 0.9s, 5.46s, 0.6s
for 2400 recs : 1.65s, 13.0s, 1.12s
…
for 4800 recs 2nd fails saying “insufficient memory”…
why is it so slow???
Hi Majo George,
You can get dates between periods by these two ways:
1.
select CAST(‘2009-03-01’ AS DATETIME )+ a.Number
from master..spt_values a
WHERE Type = ‘p’
AND number<30
2.
;with cte as(
select CAST('2009-03-01' AS DATETIME) AS Dt
UNION ALL
select Dt + 1
from cte
WHERE dt <= '2009-03-30'
)
select *
from cte
Let me know if it helps you.
Thanks,
Tejas Shah
I need to insert a journal entry transaction from GP GL20000 open table into GP GL30000 history table. The columns are the same. It is a multiple line journal entry. Once I get the Journal Entry inserted into the historical table I can easily delete the multiple line record in the open table.
Thank you for your assistance.
I need a insert to enter 368 values into a table:
a column CRT must have a incremental value starting with 1. an example : i need 1,2,3 etc…or something 36801, 36802 etc…can you help me with this?
thanks in advance!
select number from master..spt_values
where type='p' and number between 1 and 368
If you do something like
insert into table (autoincrement, name) values (”, ‘eliezer cazares’);
your autoincrement field will be filled by itself.
About inserting 368 rows, either way you come up with an iteration in some programming language or you copy and paste 368 times the query just like the “MySQL (so-called)-Authority” says.
Can i insert records into cursor from multiple cursors at a time?
I have a form with 2 input text boxes where i want to enter a number value in each. One input box in the start number (example: 5) and the second is the end number (example: 26). I want to be able to print each number from beginning to end incrementing by 1 example 5 6 7 8 until 26, each number on each page of paper, in total 22 pages.
Another example start number 48 end number 52; 48 49 50 51 52 total of 5 pages.
Can anyone help me or give me a tip.
Thanks in advance
select number from master..spt_values
where type=’p’ and number between your_start_number and your_end_number
Hello Sir,
I want to Import or can say convert a MS-Exess
File into the SQL Sever 2005 EXPRESS.
so plz tell me how it is possible?
And can i need to put the same fields in both?
Thanks
Hello Sir,
I want to Import or can say convert a MS-Exess
File into the SQL Sever 2005 EXPRESS.
so plz tell me how it is possible?
And can i need to put the same fields in both?
And i want to know the automatic Primary key allotment process.
Thanks
Hi
I have a Insert Trigger on Table A but when i inserts multiple rows in a single insert statement the trigger fires only once.
Is there any solutions to this, plz help me to solve this problem.
I want to invoke the trigger for every row insert from the single insert statement.
Thx
Karthik
Hi!
i m not getting how to insert records into ms-access in ascending order using java.i hav tried order by clause.it takes data in resultset in ascending order but does not insert that data in ascending manner in access.
please give me solution.
Hi Pinal,
Could you please answer my query?
I have a python script where in I have an insert statement in a loop. That means the insert statement executes same number of times as the loop executes.
To improve the performance I used your way of “UNION ALL” to insert 1000 rows at a time.
But when I execute this new script, for inserting 2000 rows, the performance has become lower than it was in the original script.
I insert 34 columns per row.
Please help.
Thanks in Advance.
@Neelam, try less records. Like 100. I’m just guessing here though.
@pradeep.
To ensure order, INSERT them separately.
@Karthik
The documentations says: An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.
So, it will only execute once. If you want it for every record, perhaps you can identify which records were INSERTed.
Hi Pinal,
I have error with code “ORA-00936: missing expression”. What am I missed? Thanks.
MERGE into cm_adm_usergrants c
using (select id, name from smart.user$ where id=223) o
on (c.userid=o.id)
when not matched then
insert (userid, pluginid) values
(select userid, pluginid from cm_adm_usergrants where userid=117)
when matched then
update set c.pluginid=(SELECT pluginid
FROM cm_adm_usergrants where userid=117);
@Mikail
ORA is an Oracle error, not SQL Server.
hi this is sravan.
i want insert rows from one table to another with out duplicating the rows.
then how can i write a sql command? plz tell me
thnx
sravan
I’ve had some really interesting findings, with adding multiple rows at a time.
I’ve found that when adding around 30,000 rows of data (three columns), 20 rows at a time is optimal! 100 rows at a time is a bit slower and the more you add, the slower it goes!
I can’t explain WHY, just from my observations.
BTW, I’m also in the position of wanting to add data from a file where some of the rows need to be inserted and some updated (i.e. an overlap).
I’m creating a temporary table (based on the old table), inserting the data from the file in to the new table (using the UNION ALL technique), wiping data from the table I want to update (where there is data in the temporary table, one SQL command to do that) and inserting the data from the temporary table to the table I want to update (again, one SQL command to do that).
I really wish SQLServer had MySql’s ON DUPLICATE KEY feature!
@sravan
If you want to restrict what goes in, use DISTINCT.
If you want to restrict if it is already there, use WHERE NOT EXISTS()