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 dave i have a problem.i have 1 textbox control,1 label,1 button.in my database i have create a table and thge column name is date datatype is datetime.so how to convert datetime format to dd/mm/yyyy in my database and how to retrieve the table values into a label when i click the button in vb.net.
You dont need to worry about the formation. Make sure to input dates in YYYYMMDD HH:MM:SS format. Refer this for more informations
Hi,
I need to insert multiple rows in to a table with one column is same, which i ll get through sub query(select).
Thanks in advance for your help.
Thanks
Rukmangada
insert into table(column) select column from table
I have a table control UI in my form. I want to post the cell data to the sql server DB.
I have done insert data from text box control, to a valid table field in the database. Note: the asp table control, n the table of DB use the word “table”. Table is populated with data from one table, and some of the cells’ data has to be written to another table in the same DB. Would some one help me with taking my cell data to the server? Thanx.
What if i have an error row? The whole dataset gets rejected. How can i find the error row when inserting/updating data in batch?
It will not execute and no rows will be added to the table. If you double click the error message it will point it to the line where problamatic data are there
Hi,
I have a tabel ItemTransactions (of invoice module).
If I insert 5 rows for the first time (during temporary Save), I have no problem.
Now the situation is like this.
If I open the invoice again and do any of the following
– add more rows
– delete some rows
– change the value in some rows
Currently what I do is:- delete all currrent rows from the table then insert all rows again
Is there any other better option?
Please advise
Delete all rows and insert new rows again. Thats the good method
I have a code:
insert into absence (absence_id, people_id, startdate, starttime, enddate, endtime, absencetype, calcduration)
(
select newid(), p.people_id, t.[start date], t.[start time], t.[end date], t.[end time], ‘SICK’, ‘T’
from tongasickness t
inner join people p on p.fullname = t.[Employee Name]
)
that gives the error:
Msg 512, Level 16, State 1, Procedure OC_AUDITTRIGGER_ABSENCE_INSERT, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.
Why am I getting this error?
Try this
insert into absence (absence_id, people_id, startdate, starttime, enddate, endtime, absencetype, calcduration)
select newid(), p.people_id, t.[start date], t.[start time], t.[end date], t.[end time], ‘SICK’, ‘T’
from tongasickness t
inner join people p on p.fullname = t.[Employee Name]
How to insert 25000 record in inserted at one time.
i have ready query to insert in sql server 2008.
but give Error
Msg 10738, Level 15, State 1, Line 24999
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.
Note that it is restricted to 1000 rows. If you want to insert more than 1000 rows, have data in text file and use bcp or bulk insert
Hello Folks,
Great Resource – Thank you all. I need your help with the following.
Have two tables:
1. materialleaf, with columns materialsid (numeric (10,), PK) and description (varchar(100))
2. materialkeyword, with columns materialsid (numeric (10,0), PK, FK to table 1) and keyword (varchar(50), PK) [you guessed it, materialsid in table 2 is FK to its corrsponding column in table 1)
Problem/Requirement:
I need to parse the description field in table 1 based on a ‘ ‘ (blank space) or a ‘,’ delimiter and enter the various bits as keywords in separate rows in table 2 ALONG with the materialsid from table 1.
Example:
Table 1: MaterialSID = 1000, Description = “5 Inch Philips Wrench”
Material SID = 2000, Description = “Firex Fire Extinguisher”
Table 2 should look like: 1000 5
1000 Inch
1000 Philips
1000 Wrench
2000 Firex
2000 Fire
2000 Extinguisher
Can someone please help with a script/function/SP for SQL Server 2005?
Thanks much!
Dear All,
How to Update Multiple Records Using One Update Statement.
Regards,
Jayaram.
What is wrong with update statement? Did you use it?
Thanks for this post! Not much of a difference using this method (only 1 second faster but regardless, still faster) though this makes the code much cleaner in my eyes!
Cheers!
I have to admit I am disapointed by the UNION performance I saw a couple of place telling that it’s faster. In my case it is MUCH SLOWER. I have tried using it for 50k records and the query just times out.
I can do it in 2 min which i found slow but with the UNION 5 minute is not enough.
I have tested a few methods and indeed UNION is faster but you have to limit the number of UNION to optimise it. Instead of executing 1 big SQL statement with 50K UNIONS, I do 1000 SQL querys with 50 unions in each query. I have been able to make it 4 time faster than making each of the 50k inserts individually.
50K unions is way too slow after the 50 minutes the query was still running… From my testing in my environement 50 union is my optimal .
Thats informative. Thanks
I notice the original posting is all about insert-by-union-select, but as several commenters have asked about how to improve insert performance in general, here are some more recommendations:
– Increase ADO.NET BatchSize
– Choose the target table’s clustered index wisely, so that inserts won’t lead to clustered index node splits (e.g. autoinc column)
– Insert into a temporary heap table first, then issue one big “insert-by-select” statement to push all that staging table data into the actual target table
– Apply SqlBulkCopy
– Choose “Bulk Logged” recovery model instad of “Full” recovery model
– Place a table lock before inserting (if your business scenario allows for it)
Taken from
THANK YOU SO MUCH. IT REALLY HELPED ME A LOT
Dear Pinal,
I am very thanks full to you, Because you have made it very easy for me to learn SQl. I have developed confidence in me just because of your tutorial.
Regard:
Ashish Jain
Thanks for the solution! Really helpfull.
Usual set of insert queries was running for about 5 seconds in my case.
Now it is less than 1 second.
I have a question about using UNION to create tables. In the case:
SELECT * INTO TableName
FROM (
SELECT x
FROM Table1
WHERE x = ‘1’
UNION
SELECT y
FROM Table2
WHERE y = ‘2’
) as X
ORDER BY Z
In the above case, when does the ORDER BY get applied? Is it applied to each statement, and the result appended together?, or, is the result set created in a memory table, then, the table created ordered by Z from the UNION’ed result?
First result is created and last it is ordered. But there is no gurantee that the order by work correctly. You should always use order by clause when using select statement
thanks dude… nice work
INSERT INTO books(thec_code, room_number, status)
SELECT ‘C01′,’R115′,’Confirmed’
UNION ALL
SELECT ‘C02′,’R210′,’Reserved’
UNION ALL
SELECT ‘C04′,’R375′,’Cancelled’
UNION ALL
SELECT ‘C04′,’R455′,’Reserved’
GO
It’s not working…
What’s wrong??
:(
ahhh… i got it!!!
it should be GO;
and not simply GO
:)
this is actually a MUCH faster method:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5);
GO
This is SQL2008 only