How to using SQL insert multiple rows is a very popular question and in this tutorial I will show you several different ways to do it. For the purpose of this tutorial I will SQL Server 2008 R2 but this should still work with SQL Server 2008. The first example I will show will not work in SQL Server 2005 but the rest of examples should work with 2005 version as well.
 For the purpose of this tutorial I will use a table called dbo.InsertMultipleRows which contains two fields Field1 nvarchar(50) and FIeld2 int. See screenshot.
SQL INSERT INTO - Multiple rows
In the first example I will show you how to use SQL INSERT INTO using VALUES where I will hard code multiple rows.
The basic SQL INSERT INTO example is:
INSERT INTO dbo.InsertMultipleRows (Field1, Field2)
VALUES ('value1',1)
To insert multiple rows using SQL Server 2008 onward we can simply but comma after each "row" in VALUES. See below examples with screenshot.
INSERT INTO dbo.InsertMultipleRows (Field1, Field2)
VALUES ('value1',1)
,('value2',2)
,('value3',3)
,('value4',4)
,('value5',5)
So that is how you can using sql insert multiple rows at once
SQL Server Insert multiple rows using 2005 version
The above example should work in SQL Server version 2008 onward but might not work in 2005 but you can still insert multiple rows at once using SQL.
Below is an example of 3 separate inserts. This method has its limitation like I think SSMS query window has some limit of insert. I think 1000 but I might be wrong with this figure. Also performance is sometimes important so this method still might work better than the next one but it will depend on number of rows you have.
INSERT INTO dbo.InsertMultipleRows (Field1, Field2)
VALUES ('value3',3)
INSERT INTO dbo.InsertMultipleRows (Field1, Field2)
VALUES ('value3',3)
INSERT INTO dbo.InsertMultipleRows (Field1, Field2)
VALUES ('value3',3)
Below I will use SQL INSERT INTO just once and SELECT with UNION ALL to peform multiple insert at once. It is important to note that I used UNION ALL which does not remove duplicates which is not always what you want. If you have source that contains duplicated rows and you want to remove them than use UNION which performs DISTINCT row operation which removes duplicates; UNION and is usually slower than UNION ALL.
This method should work but be aware that it might become inefficient in certain scenarios. Althought I haven't tested it yet. It seems this might take a bit longer on large datasets. I can understand UNION as it performs distinct operation which might explain slow operation on large datasets however UNION ALL shouldn't have in thoery this side effect. This is something I will have to test at some point. If you have any details on that. Let me know and I will include that in this blog post.
INSERT INTO dbo.InsertMultipleRows (Field1, Field2)
SELECT 'value1' AS Field1, 1 AS Field2
UNION ALL
SELECT 'value2' AS Field1, 2 AS Field2
UNION ALL
SELECT 'value3' AS Field1, 3 AS Field2
|
