How to do multi-row insert?

Folks, forgive me if I'm missing something obvious, but I can't find anything about this on MSDN or Google. I'm new to SQL Server, my background is DB2. I'm working on porting an existing open-source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend.

The Gallery application has a multi-row INSERT statement that (AFAIK) works on all other RDBMS, but fails on SQL Server. I can reproduce it easily with:

create table ljmtemp (col1 int, col2 int, col3 int, col4 char(20)

insert into ljmtemp values (6565,0,2,'scale|654'), (6566,0,2,'scale|654'), (6567,0,2,'scale|654')

Msg 102, Level 15, State 1, Server CS288290-A\SQLEXPRESS, Line 1
Incorrect syntax near ','.

If I specify only one row in the VALUES clause it works. Does SQL Server not support multi-row inserts?

Thanks.

YOu could do something like what you call multi-inserts, but SQL Server does not have a syntax like you wrote. The insert of multiple records can be done with using a Select statement. So if you want to insert some data of another table it would be

INSERT INTO TableName
(
Columnlist -- Preferable way to name the columns in here
)
SELECT Columnlist
FROM AnotherTable

OR if you really want to do that with statics, it would be something like:

INSERT INTO TableName
(
Columnlist -- Preferable way to name the columns in here
)
SELECT * FROM
(
SELECT 6565,0,2,'scale|654'
UNION
SELECT 6566,0,2,'scale|654'
UNION
SELECT 6567,0,2,'scale|654'
) UnionQueryName

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the reply Jens.

Yes, I do have to use static values. I tried your suggestion and it didn't work:

1> insert into ljmtemp (col1, col2, col3, col4) select * from (select 6565,0,2,'scale|654' union select 6566,0,2,'scale|654' union select 6567,0,2,'scale|654') g2union
2> go
insert into ljmtemp (col1, col2, col3, col4) select * from (select 6565,0,2,'scale|654' union select 6566,0,2,'scale|654' union select 6567,0,2,'scale|654') g2union

Msg 8155, Level 16, State 2, Server CS288290-A\SQLEXPRESS, Line 1
No column was specified for column 1 of 'g2union'.
Msg 8155, Level 16, State 2, Server CS288290-A\SQLEXPRESS, Line 1
No column was specified for column 2 of 'g2union'.
Msg 8155, Level 16, State 2, Server CS288290-A\SQLEXPRESS, Line 1
No column was specified for column 3 of 'g2union'.
Msg 8155, Level 16, State 2, Server CS288290-A\SQLEXPRESS, Line 1
No column was specified for column 4 of 'g2union'.

Did I get something wrong? Or do you have any other suggestions?

|||

I got it to work as follows:

1> insert into ljmtemp (col1, col2, col3, col4) select 6565,0,2,'scale|654' union all select 6566,0,2,'scale|654' union all select 6567,0,2,'scale|654'
2> go
insert into ljmtemp (col1, col2, col3, col4) select 6565,0,2,'scale|654' union all select 6566,0,2,'scale|654' union all select 6567,0,2,'scale|654'

(3 rows affected)

Thanks for the help.