One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.
In the following quick video we have demonstrated three different methods to insert multiple values into a single table.
-- Insert Multiple Values into SQL Server
CREATE TABLE #SQLAuthority (ID INT, Value VARCHAR(100));
Method 1: Traditional Method of INSERT…VALUE
-- Method 1 - Traditional Insert
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (2, 'Second');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (3, 'Third');
Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;
Method 2: INSERT…SELECT
-- Method 2 - Select Union Insert
INSERT INTO #SQLAuthority (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third';
Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;
Method 3: SQL Server 2008+ Row Construction
-- Method 3 - SQL Server 2008+ Row Construction
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third');
Clean up
-- Clean up
DROP TABLE #SQLAuthority;
[youtube=http://www.youtube.com/watch?v=T4054BuE2Vg]
Related Tips in SQL in Sixty Seconds:
- SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL
- SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor
I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.
If we like your idea we promise to share with you educational material.
Reference: Pinal Dave (https://blog.sqlauthority.com)
30 Comments. Leave new
any difference in speed?
Performance side, which one is the best choice… ?
I don’t think, we need to use this query where performance matters. Sometimes easiness matters more than performance.
Using of BULK INSERT
STEP 1: Create a text file the name STUDENTS_LIST.txt and add the bellow data
1,RAJIV,M.S
2,VINAY KUMAR,B.A
3,HANU,M.B.A
4,ARUN,M.Sc
STEP 2: Create a table using bellow statment
CREATE TABLE #STUDENT(ID INT, [NAME] NVARCHAR(50), EDUCATION NVARCHAR(20))
STEP 3: Now use the Bulk Insert Statement
BULK INSERT #STUDENT
FROM ‘D:STUDENTS_LIST.txt’
WITH ( FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘n’ )
STEP 4: Check the table using bellow query
SELECT * FROM #STUDENT
DROP TABLE #STUDENT
and one more thing is using user defined table types in Sql Server 2008
Check example @
What about Table Valued Paremeters ?
http://www.sqlservercentral.com/articles/SQL+Server+2008/67550/
Your biggest risk if you’re just looping over a list to create the insert is that you may exceed the 2100-parameter limit in .Net SqlCommand creation.
TVP works fine for to avoid this. But Bulk Insert is slightly faster in my tests. Both are much faster than comma-separated values.
I just checked the execution plans for each query. Performance wise all of the queries should cost approx. the same. Only difference i saw is that the first query is a direct insert. the other two use ‘Constant Scan’,’Top’,’Compute Scalar’ (which all cost 0%) in the execution plan and then the values are inserted into the table. Also value wise (CPU time etc.) the second and the third query seem to be the same.
How can I submit my ideas for ‘SQL in Sixty Seconds’?
In SQL 2008 you can use up to 1000 comma-separated clauses on the VALUES section
e.g.
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, ‘First’),
VALUES (2, ‘Second’),
VALUES (3, ‘Third’);
This is useful for ad-hoc SQL, but I agree with the earlier posters that normally, table-valued parameters are the way to go for inserting multiple records. If you are stuck with SQL2005 you can use XML.
Interesting, but there’s another limit many of us would hit first.
If you’re executing from inside a .Net program then you’ve a limit of 2100 parameters per command. This toy example with 2 columns is okay, but in the real world you would probably blow up before you got to 1000 rows.
Hi Pinal Dave,
Is there any menthod which gives me the last inserted n records of the table which does not have IDENTITY COLUMN?
Mayura – you can use the OUTPUT clause to return the records that were just inserted.
Alan, thank for the reply. OUTPUT clause will not serve my requirement, if inserts had happened from different sessions. My requirement is to get last 10 inserted records from all tables which have millions of records in each table.
I am able to get it using IDENT_CURRENT(‘tablename’) for the tables which has IDENTITY COLUMN. Looking to get n reocrds from tables which don’t have IDENTITY COLUMN. I am just wondering is there any un-documented View, SP, Table etc. which gives me the last inserted n records.
Mayura – just add a created_datetime column to the table with a default of GETUTCDATE(). Put an index on that, then you can do:
SELECT TOP 10 * FROM yourtable ORDER BY created_datetime DESC
I don’t wanted to add any column to the table neighter alter my indexes.
I wanted to know will SQL server maintains any field thru that we can get the records.
Not sure if this is the correct forum or subject to make a post, but I’ll give it a shot…
I have created a view in a very complex MS-SQL db that runs an application.
The ultimate goal is to display the data and have a few fields where users enter a few items in Sharepoint… (Example)
CustomerName assessmentdate Invoicechecked Invoicesent
bob jones 09/01/2012
so above data from db is displayed (custname, assessdate) but in sharepoint users would answer yes/no etc for invoicechecked/invoicesent
I created a view, mainly because quite a few tables had to be joined etc to pull data and I obviosuly don’t know what I am doing…
The data in the view looks from today back 15 days. (keep that in mind) That means items will fall off the view and items in view will change. I want the items in the view to copy over to table on a regular basis. Because the view is always looking back 15 days there will be items in the view that will be duplicates or will have changed…don’t know how to deal with that either
At the same time the Table has fields that will be null from beginning because they will be populated via Sharepoint.
any ideas or help would be much appreciated!
— Or you can Simply populate multiple values
— from the results of a select statement
— as long as the datatypes match
INSERT INTO Table2
([col1]
,[col2]
,[col3])
SELECT col1, col2, col3 FROM table1
–||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
/* FULL QUERY BELOW */
–||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
— Create two tables
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
[col1] [int] NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2](
[col1] [int] NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL
) ON [PRIMARY]
GO
— Populate the first table
INSERT INTO Table2
([col1]
,[col2]
,[col3])
select 1, ‘hi1’, ‘Dave1’
UNION ALL
select 2, ‘hi2’, ‘Dave2’
GO
— Populate Table 2 with the values of Table1
INSERT INTO Table2
([col1]
,[col2]
,[col3])
SELECT col1, col2, col3 FROM table1
GO
— Check the values of Table2
SELECT
col1
, col2
, col3
FROM Table2
GO
(or any other valid SQL Select statement where the datatypes from the source match the datatypes of the destination.)
hi, I have sql question: how can I SSIS export into one text file from several different tables: have different columns , each table has one knid of record type.
Data in exported text file something like:
Head record: 1,6701,T1234,101(total length20)(from table1)
Record type 2: 2,10000000087,12/02/2012,…….(total length 300)(from table2)
Record type 10: 10, 64538, 03/12/2012,1(total length 15)(from table 3)
Record type 28: 28, 1000000087,,2(total length 65)(from table4)
I tried UNION ALL and it works, but record total length like record type 1 is not 20 , instead of 300 as table2.
Thanks.
hello…. sir,
i want to store multiple values in one tuple…
(e.g my project is restaurant billing ) so how can i store multiple items with single bill no…. it generates multiple rows with every new item entry in database for one bill… ..
please help me sir…..
thank you….
Hello Every one,
Is there any way to store multiple images in image field? I am using sql2005
Thanks in advance,
Jaison
what a blog
You forgot to mention SQLBulkCopy . It’s faster for large data.
well, How do I add values on a column that I just added to a table? When trying to add the values with “insert into TABLE (new_column) values (‘a’)” the SQL server creates a new row instead of putting this value on an existing row! How do I add values on existing row (completing it) on new added columns?
Insert is to create new row. If you want to add new value to a column in existing row then you have you use update statement.
I want one solution in VB 2010 with MS SQL 2008 data connection, so please could you help me on this topic
I have multiple table; table1, table2 & table3
In which I want to inset multiple record in single quarry, so please guide us how it is possible
If any error occur in any of table during insetting record, no one data will save in any table all record to be rejected
Is this possible ?
Please guide me
Pintu Mistry
–@i => number of row,
declare @i int = 1
while (@i <= (SELECT TOP 1 id FROM table2 ORDER BY id DESC))
begin
INSERT INTO table1(col1,col2)
values
((select col1 from tabl2 where id = @i) ,(select col2 from table2 where id=@i))
set @i = @i+1
end
Great post. I want to know if multiple insert is best or single insert?
Great post!
Why the multiple row insert won’t fire triggers?
Can you do a post sorting that out?
All the best,