Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

Question

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports".

Here's what I want to do, but the syntax is not exactly right... please, someone who has done this before, help me out :)

INSERT INTO dbo.MyTable (ID, Name) VALUES (123, 'Timmy'),     (124, 'Jonny'),     (125, 'Sally') 

I know that this is close to the right syntax. I might need the word "BULK" in there, or something, I can't remember. Any idea?

I need this for a SQL Server 2005 database. I've tried this code, to no avail:

DECLARE @blah TABLE (     ID INT NOT NULL PRIMARY KEY,     Name VARCHAR(100) NOT NULL )  INSERT INTO @blah (ID, Name)     VALUES (123, 'Timmy')     VALUES (124, 'Jonny')     VALUES (125, 'Sally')  SELECT * FROM @blah 

I'm getting Incorrect syntax near the keyword 'VALUES'.

Answer 1

Your syntax almost works in SQL Server 2008 (but not in SQL Server 2005¹):

CREATE TABLE MyTable (id int, name char(10));  INSERT INTO MyTable (id, name) VALUES (1, 'Bob'), (2, 'Peter'), (3, 'Joe');  SELECT * FROM MyTable;  id |  name ---+--------- 1  |  Bob        2  |  Peter      3  |  Joe        

---

¹ When the question was answered, it was not made evident that the question was referring to SQL Server 2005. I am leaving this answer here, since I believe it is still relevant.