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
To the last poster, here’s the MSDN link: https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017
INSERT INTO dbo.State(StateName)
VALUES (‘Alaska’), (‘Arkansas’), (‘Alabama’);
Hi Pinal,
I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
My question is how to insert 400,000 rows at a time in a table.
Can you let me know the script to generate it.
Please do help me out.
Thanks in Advance.
Hi Pinal,
I have tried to use you way of inserting multiple records.
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
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_tableName’. Cannot insert duplicate key in object ‘tableName’.
The statement has been terminated.
I usually use excel formula to create insert into statement. It helps me to upload more than a millions of records in a minute. Those who do insertion in the database quite frequently, they can use my technique that use excel formula.
Zahid
Sorry, I should work, I have check my error msg and it is a primary key violation.
Dear Reader,
you can use either Pinal’s process ro my process as you like.
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.