Insert multiple rows into a table in SQL Server

Question

I am using SQL Server. I am trying to figure out how to insert multiple rows with a single query.

In MySQL, the query will look like this:

the 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 message:

Invalid syntax next to ','.

Is there a way to do this in SQL Server?

Answer 1

This syntax will work in SQL 2008; in SQL 2005 you have to do SELECT and UNIONs

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

Answer 2

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