This is a very interesting question I have received from new development. How can I insert multiple records in a table using only one insert? Now this is an interesting question. When there are multiple records to be inserted in the table, the 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);
GOThe short answer today
If you are on SQL Server 2008 or anything newer, which is everybody by now, one INSERT can carry all the rows. Separate each row with a comma and you are done.
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First', 1),
('Second', 2),
('Third', 3),
('Fourth', 4),
('Fifth', 5);
GOThis is called the row constructor, or the table value constructor if you like the long name. It is shorter to write, easier to read, and it is one statement rather than five, so the server does less work. Start here.
One limit worth remembering: a single VALUES list can hold 1,000 rows. Go past that and SQL Server refuses. If you have more rows than that, split them into batches of a thousand, or load them from a file instead.
The UNION ALL way, and why it is still here
The clause INSERT INTO is repeated multiple times above. Many times DBA copy and paste it to save time. There is another alternative to this, which I used for years before the row constructor arrived. I use UNION ALL and INSERT INTO … SELECT… Clauses.
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. Two reasons this one has not gone away. It has no thousand row limit, so it is handy for a long generated script. And every row is a SELECT, so you can mix in expressions, GETDATE, or a lookup from another table on any line without changing the shape of the statement.
Use UNION ALL and not UNION. UNION quietly removes duplicate rows, so if two of your rows are identical, one of them will never reach the table and you will spend an afternoon working out why.
If the rows already exist somewhere
Plenty of people land here wanting to copy rows from one table to another rather than type them out. That is a different statement, and a simpler one.
INSERT INTO MyTable (FirstCol, SecondCol) SELECT FirstCol, SecondCol FROM SomeOtherTable WHERE SecondCol > 10;
No VALUES, no UNION ALL, and no row limit. The column list on the INSERT and the column list on the SELECT have to line up in order and in type, which is the only part people get wrong.
Which one should you use
Up to a thousand rows that you are typing by hand, use the row constructor. More than a thousand, or rows built from expressions, use UNION ALL. Rows that already live in another table, use INSERT INTO … SELECT. Thousands of rows arriving from a file, do not use any of these, use BULK INSERT.
Update: the row constructor shown above first arrived in SQL Server 2008, and I wrote about it separately here: SQL SERVER Insert Multiple Records Using One Insert Statement – Use of Row Constructor
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
875 Comments. Leave new
I don’t have any records to insert.I need to insert new rows.How do I enter millions of rows into a table at a time.
Please let me know the Script?
Can we use While loop or something like that.
Please help me out.
Samyuktha
Where is the source data coming from? If it is a table, just use this
insert into target_table(cols)
select cols from source_table where
how write table of 2 with cursor in MS SQL ?
Can you give more information on what you want to do?
I’m having 2 tables namely customer,product.The customer table consist 2 field namely custid and custname and product consist 3 fields namely prodid,prodname,date. i need to insert data from both table with single insert command and here when i select custname this particular id will be inserted into product id. Pls help…..
Select AcType, MainCode, Balance from Master where AcType between ’01’ and ’07’ and Balance>100000
Union
Select AcType, “~”, SUM(Balance) from Master where AcType between ’01’ and ’07’ and Balance>100000
Group by AcType
Order by AcType
Result will be
Head Office
Wednesday, December 7, 2011
07/12/2011 3:51:32 PM User : RAJEEV Station : GFL Page : 1
—————————————————-
AcType MainCode Balance
—————————————————-
01 00101086GP 193,104.78
01 ~ 193,104.78
04 00100427Y5 276,045.42
04 00100620Y1 255,202.44
04 00100751IA 128,244.86
04 00101157IA 462,052.60
04 ~ 1,121,545.32
05 00100865GP 190,150.05
05 00101298GP 166,621.25
05 ~ 356,771.30
07 001000000107 167,489.78
07 001000000307 155,108.62
07 001000000407 431,436.82
07 001000000507 316,170.47
07 001000000907 239,352.41
07 001000001107 154,044.20
07 001000001307 267,387.29
07 001000001407 990,851.57
07 001000001607 263,150.63
07 ~ 2,984,991.79
==================== (End of Report)
—- > Now I want to do as follows
Head Office
Wednesday, December 7, 2011
07/12/2011 3:51:32 PM User : RAJEEV Station : GFL Page : 1
—————————————————-
AcType MainCode Balance
—————————————————-
01 00101086GP 193,104.78
01 ~ 193,104.78
04 00100427Y5 276,045.42
04 00100620Y1 255,202.44
04 00100751IA 128,244.86
04 00101157IA 462,052.60
04 ~ 1,121,545.32
05 00100865GP 190,150.05
05 00101298GP 166,621.25
05 ~ 356,771.30
07 001000000107 167,489.78
07 001000000307 155,108.62
07 001000000407 431,436.82
07 001000000507 316,170.47
07 001000000907 239,352.41
07 001000001107 154,044.20
07 001000001307 267,387.29
07 001000001407 990,851.57
07 001000001607 263,150.63
07 ~ 2,984,991.79
Grand Total 4,656,413.19 <—- "GRAND TOTAL"
==================== (End of Report)
Is This Works for Delete?
No, Union all cannot be used to delete rows from a table
FYI – I have done extensive research regarding this original post, and according to my research the fastest way to insert bulk data via SQL is to use a Table Variable as follows…
DECLARE @TblVar table (FirstCol nvarchar(50), SecondCol int)
INSERT INTO @TblVar
VALUES (‘First’,1);
INSERT INTO @TblVar
VALUES (‘Second’,2);
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT * from @TblVar
Sure it is not a single statement. But… it was about 4 times faster than the UNION ALL method.
For large numbers of records I got better performance by grouping 1000 records at a time (ie repeating the SQL above for every 1000 records). I ran each group of 1000 records as a separate script.
I welcome any feedback…
Pinal,
Hmmm I just noticed the update you wrote at the top.
(ie 2008 method of Row Construction).
I wonder how my method compares in performance.
I might give it some testing.
Wow, in my tests the 2008 method is actually about 2 x slower than the Table Variable method. AND it is limited to 1000 rows.
This page really great…
Please give me a script that once i entered four digit numbers i can insert it to the database as many as i can instead of inserting it one by one.. i have here the script but i dont know how to insert it even 100 times in one click.
$sqllucky = “INSERT INTO lucky (userid,lucky1,lucky2,lucky3,lucky4,ldate) VALUES (‘$userid’,’$lucky1′,’$lucky2′,’$lucky3′,’$lucky4′,now())”;
$luckyresult = connect($sqllucky);
that snippet can inserted one by one what if i want to insert 10 times…please help me…
insert into #MangeshTemp
values
( ‘Test1’, ‘Test2’ ),
( ‘Test3’, ‘Test4’ );
.
.
.
so on and so forth…
you can use a advanced text editor with a macro to produce your insert statement.
forgot to specify columns, but you get the idea…
SQL 2008
Insert into DCGRPMBR (CAMPAIGNYEAR, GROUPCONTRACTACCOUNT, GROUPCONTRACTSORTFIELD, MEMBERACCOUNT, MEMBERACCOUNTTYPE,
MEMBERSORTFIELD, NOTESET, LASTCHGDATE, LASTCHGTIME, LASTCHGUSER, CREATEDATE, CREATETIME, CREATEUSER)
Values (2010, 2527927, ‘GOLUB/PRICE CHO’,
(Select DWACCOUNTS.DWORGACCOUNT
from DWACCOUNTS where DWACCOUNTS.DWNAME in (‘SCA_PCGC’)),
‘O’, PCS’, 0, 20120216, 105610, ‘SALBERTIN’, 20120216, 105611, ‘SALBERTIN’)
I have a table (SCA_PCGC) that contains 126 rows with one column, an ID. I would like that data from each row to be inserted into an existing table (DCGRPMBR) along with values that I manually enter in the code. The code above works fine when there is only one record in the SCA_PCGC table, however it fails with the error:
“Msg 512, Level 16, State 1, Line 1
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.”
Whenever I have more than one record in the SCA_PCGC table, I get the above error.
Ideally I would like the code to run through each of the 126 records in the SCA_PCGC table and insert them into the DCGRPMBR table with each of the values. So I would end up with 126 new records in DCGRPMBR:
One Row with the following information for each of the 126 records
2010, 2527927, ‘Golub/Price Cho’, ID FROM SCA_PCGP, ‘O’, ‘PCS’, 0, 20120216, 105610, ‘SALBERTIN’, 20120216, 105611, ‘SALBERTIN
I am new to this and have tried different variations of what I have found for multiple row inserts. Any help is greatly appreciated.
if you examine the inserted tables after two operations
first one results multiple times 1 row(s) effected rows
but second one says one time but multiple row(s) effected
select sum(salary) from EMPloyee limit 10 to 50
Hi all ,
I want to use this same technique in MySQL and MsAccess databases.
Anyone knows how to implement in that two databases as SQL Server.
Thanks,
Prabhakaran.K
hi all
I have created a table using phpmymin with 3 fields(id, name contactno) . Now my data is in csv file. Please tell me a command to insert the data presented in the excel sheet into the table using phpmyadmin
how to insert data into multiple table through the use of one insert query.
why is unionall faster than multiple insert …is it true for all conditions or in some particular conditions
how in one insert commend ican insert multiples rows ?
hi sir,
this blig is good and helpful for me….
but how can i insert N no’s of records when we are not Knowing exactly records
plz give me this kind of query solution to improve my query….
Hello Sir,
This blog is very good and very help full to me to become a bda
Thks