...

View Full Version : SQL Server - Inserting multiple rows in one query



melissa820
03-14-2007, 01:42 PM
I am used to using MySQL but had to transfer to SQL server for work. I'm trying to figure out how to insert multiple rows with one query. In MySQL the query would be like this:


INSERT INTO Mytable (Name, Number) VALUES ('Joe', 18), ('Bob', 25), ('Mike', 7);

I tried a query like the one above in SQL Server and it gave me an error that said: Incorrect syntax near ','.

Is there a way to do this in SQL Server? Or will I have to resort to doing multiple insert statements?

Roelf
03-14-2007, 11:24 PM
What you can do is faking a subselect. In MSSQL, you can do a multiple insert by:

INSERT INTO thetable (field1, field2)
SELECT field1, field2 FROM table2
But this won't work for your situation, because it is new data. What you can do now is enter the data after the SELECT statement, as it came from another table. There you stray away from standard SQL, but in MSSQL it will work. It looks like this:

INSERT INTO thetable (field1, field2)
SELECT value1, value2
UNION ALL
SELECT value3, value4
UNION ALL
.
.
.
SELECT value5, value6;

melissa820
03-19-2007, 08:50 PM
Thanks for your reply. I haven't had a chance to test this out yet, but I'm sure it will work.

anurag
06-06-2008, 02:23 AM
It looks like this:

INSERT INTO thetable (field1, field2)
SELECT 'value1', 'value2'
UNION ALL
SELECT 'value3', 'value4'
UNION ALL
.
.
.
SELECT 'value5', 'value6' [/QUOTE]
i m fully sure that it works bcoz i hav already try it.....:thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum