Sql Server – Inserting Multiple Rows With Single (ansi Style) Statement

I am using following method for inserting multiple rows using a single INSERT statement, that is the ANSI style of inserting rows. It is available in SQL Server 2008 and 2012. I am

Solution 1:

Try this instead:

INSERT TestInsert
    SELECT1, 'a'UNIONALLSELECT2, 'b'UNIONALLSELECT3, 'c'UNIONALLSELECT4, 'd'UNIONALLSELECT5, 'e'

Solution 2:

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

INSERTINTOtable-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:

CREATETABLE dual(valueINTPRIMARY KEY CHECK(value=1))
INSERT dual(value) VALUES(1)

and then

INSERTINTOtable-name (columns) -- the columns are not mandatorySELECTvaluesFROM dual
UNIONALLSELECT another-valuesFROM dual
UNIONALLSELECT another-valuesFROM 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,

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

will work on SQL Server 2008+.