SQL Server 2005: Insert Multiple Rows with Single Query

SQL Server 2005: Insert multiple rows with single query

Yep. You have to use UNION ALLs in SQL Server 2005 to insert multiple rows in a SQL script in a single statement.

INSERT INTO Table 
  (Name, Location) 
SELECT 'Name1', 'Location1' 
UNION ALL
SELECT 'Name2', 'Location2'
UNION ALL
SELECT 'Name3', 'Location3' 

The other main alternative is to repeat the Insert statement multiple times which is even more verbose. You need to be careful to use Explicit transactions in this last case to avoid the overhead of many individual commits (and for atomicity reasons of course)

If you have lots of rows to insert you could use BULK INSERT to load it all in from a delimited file in one statement.

Finally if this is data already in the database that you are scripting out (perhaps to deploy on another server) the SSMS Tools Pack addin has a "Generate Insert Statements" function that can generate these statements for you.

insert multiple rows in single SQL

you have to remove UNION ALL before the first SELECT.

INSERT INTO login (LogInID,Password)
SELECT 'Name1','pass1'
UNION ALL
SELECT 'Name2','pass2'

Insert multiple values using INSERT INTO (SQL Server 2005)

The syntax you are using is new to SQL Server 2008:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test'),(1001,N'test2')

For SQL Server 2005, you will have to use multiple INSERT statements:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1000,N'test')

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
 VALUES
       (1001,N'test2')

One other option is to use UNION ALL:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'

Insert multiple rows WITHOUT repeating the INSERT INTO ... part of the statement?

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

SQL Server – inserting multiple rows with single (ANSI style) statement

SQL Server - inserting multiple rows with single (ANSI style) statement

For SQL Server 2000+

According to SQL The Complete Reference, Third Edition (August 12, 2009):

1) The syntax for multirow INSERTs is

INSERT INTO table-name (columns not mandatory) 
query

(page 236, Figure 10-3).

2) The SELECT statement has the FROM clause mandatory (page 87, Figure 6-1).

So, in this case, to insert multiple rows using just one INSERT statement we need an auxiliary table with just one row:

CREATE TABLE dual(value INT PRIMARY KEY CHECK(value = 1))
INSERT dual(value) VALUES(1)

and then

INSERT INTO table-name (columns) -- the columns are not mandatory
SELECT values FROM dual
UNION ALL
SELECT another-values FROM dual
UNION ALL
SELECT another-values FROM dual

Edit 2: For SQL Server 2008+

Starting with SQL Server 2008 we can use row constructors: (values for row 1), (values for row 2), (values for row 3), etc. (page 218).

So,

INSERT INTO TestInsert 
VALUES (1,'a'), --The string delimiter is ' not ‘...’
       (2,'b'),
       (3,'c'),
       (4,'d'),
       (5,'e')

will work on SQL Server 2008+.

Multiple INSERT statements vs. single INSERT with multiple VALUES

Addition: SQL Server 2012 shows some improved performance in this area but doesn't seem to tackle the specific issues noted below. This
should apparently be fixed in the next major version after
SQL Server 2012!

Your plan shows the single inserts are using parameterised procedures (possibly auto parameterised) so parse/compile time for these should be minimal.

I thought I'd look into this a bit more though so set up a loop (script) and tried adjusting the number of VALUES clauses and recording the compile time.

I then divided the compile time by the number of rows to get the average compile time per clause. The results are below

Up until 250 VALUES clauses present the compile time / number of clauses has a slight upward trend but nothing too dramatic.

But then there is a sudden change.

That section of the data is shown below.

+------+----------------+-------------+---------------+---------------+
| Rows | CachedPlanSize | CompileTime | CompileMemory | Duration/Rows |
+------+----------------+-------------+---------------+---------------+
|  245 |            528 |          41 |          2400 | 0.167346939   |
|  246 |            528 |          40 |          2416 | 0.162601626   |
|  247 |            528 |          38 |          2416 | 0.153846154   |
|  248 |            528 |          39 |          2432 | 0.157258065   |
|  249 |            528 |          39 |          2432 | 0.156626506   |
|  250 |            528 |          40 |          2448 | 0.16          |
|  251 |            400 |         273 |          3488 | 1.087649402   |
|  252 |            400 |         274 |          3496 | 1.087301587   |
|  253 |            400 |         282 |          3520 | 1.114624506   |
|  254 |            408 |         279 |          3544 | 1.098425197   |
|  255 |            408 |         290 |          3552 | 1.137254902   |
+------+----------------+-------------+---------------+---------------+

The cached plan size which had been growing linearly suddenly drops but CompileTime increases 7 fold and CompileMemory shoots up. This is the cut off point between the plan being an auto parametrized one (with 1,000 parameters) to a non parametrized one. Thereafter it seems to get linearly less efficient (in terms of number of value clauses processed in a given time).

Not sure why this should be. Presumably when it is compiling a plan for specific literal values it must perform some activity that does not scale linearly (such as sorting).

It doesn't seem to affect the size of the cached query plan when I tried a query consisting entirely of duplicate rows and neither affects the order of the output of the table of the constants (and as you are inserting into a heap time spent sorting would be pointless anyway even if it did).

Moreover if a clustered index is added to the table the plan still shows an explicit sort step so it doesn't seem to be sorting at compile time to avoid a sort at run time.

I tried to look at this in a debugger but the public symbols for my version of SQL Server 2008 don't seem to be available so instead I had to look at the equivalent UNION ALL construction in SQL Server 2005.

A typical stack trace is below

sqlservr.exe!FastDBCSToUnicode()  + 0xac bytes  
sqlservr.exe!nls_sqlhilo()  + 0x35 bytes    
sqlservr.exe!CXVariant::CmpCompareStr()  + 0x2b bytes   
sqlservr.exe!CXVariantPerformCompare<167,167>::Compare()  + 0x18 bytes  
sqlservr.exe!CXVariant::CmpCompare()  + 0x11f67d bytes  
sqlservr.exe!CConstraintItvl::PcnstrItvlUnion()  + 0xe2 bytes   
sqlservr.exe!CConstraintProp::PcnstrUnion()  + 0x35e bytes  
sqlservr.exe!CLogOp_BaseSetOp::PcnstrDerive()  + 0x11a bytes    
sqlservr.exe!CLogOpArg::PcnstrDeriveHandler()  + 0x18f bytes    
sqlservr.exe!CLogOpArg::DeriveGroupProperties()  + 0xa9 bytes   
sqlservr.exe!COpArg::DeriveNormalizedGroupProperties()  + 0x40 bytes    
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x18a bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!CQuery::PqoBuild()  + 0x3cb bytes  
sqlservr.exe!CStmtQuery::InitQuery()  + 0x167 bytes 
sqlservr.exe!CStmtDML::InitNormal()  + 0xf0 bytes   
sqlservr.exe!CStmtDML::Init()  + 0x1b bytes 
sqlservr.exe!CCompPlan::FCompileStep()  + 0x176 bytes   
sqlservr.exe!CSQLSource::FCompile()  + 0x741 bytes  
sqlservr.exe!CSQLSource::FCompWrapper()  + 0x922be bytes    
sqlservr.exe!CSQLSource::Transform()  + 0x120431 bytes  
sqlservr.exe!CSQLSource::Compile()  + 0x2ff bytes   

So going off the names in the stack trace it appears to spend a lot of time comparing strings.

This KB article indicates that DeriveNormalizedGroupProperties is associated with what used to be called the normalization stage of query processing

This stage is now called binding or algebrizing and it takes the expression parse tree output from the previous parse stage and outputs an algebrized expression tree (query processor tree) to go forward to optimization (trivial plan optimization in this case) [ref].

I tried one more experiment (Script) which was to re-run the original test but looking at three different cases.

1. First Name and Last Name Strings of length 10 characters with no duplicates.
2. First Name and Last Name Strings of length 50 characters with no duplicates.
3. First Name and Last Name Strings of length 10 characters with all duplicates.

It can clearly be seen that the longer the strings the worse things get and that conversely the more duplicates the better things get. As previously mentioned duplicates don't affect the cached plan size so I presume that there must be a process of duplicate identification when constructing the algebrized expression tree itself.

Edit

One place where this information is leveraged is shown by @Lieven here

SELECT * 
FROM (VALUES ('Lieven1', 1),
             ('Lieven2', 2),
             ('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID) 

Because at compile time it can determine that the Name column has no duplicates it skips ordering by the secondary 1/ (ID - ID) expression at run time (the sort in the plan only has one ORDER BY column) and no divide by zero error is raised. If duplicates are added to the table then the sort operator shows two order by columns and the expected error is raised.

Insert SQL for multiple record

If your DB is lower than SQL Server 2008

INSERT INTO PERSON (ID, EMP_NAME) VALUES ('E001', 'AAA');
INSERT INTO PERSON (ID, EMP_NAME) VALUES ('E002', 'BBB');

---

Related Topics