Insert multiple rows into table in SQL Server

Question

I am using to using SQL Server. I'm trying to figure out how to insert multiple rows with one query.

In MySQL the query would be like this:

Code:

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?

Stuart Ainsworth · Accepted Answer · 2011-10-28 16:50:09Z

5

That syntax will work in SQL 2008; in SQL 2005, you have to do SELECTs and UNIONs

INSERT INTO Mytable (Name, Number) 
SELECT 'Joe', 18
UNION ALL SELECT 'Bob', 25
UNION ALL SELECT 'Mike', 7

answered Oct 28, 2011 at 16:50

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I'm glad you posted this, but to the makers of T-SQL: Seriously, couldn't you come up with easier syntax than that!

Well, the VALUES(),() syntax will work in SQL 2008+, and since SQL 2005 is now a defunct product it all came out in the wash :)

James Webster · Accepted Answer · 2012-06-15 13:16:39Z

-1

INSERT INTO sample (ID, Name)
    VALUES (001, 'happy'),
  (002, 'sunny'),
 (125, 'rajesh')

James Webster

32.1k11 gold badges72 silver badges116 bronze badges

answered Jun 15, 2012 at 7:30

user1346121

11 bronze badge