In TSQL (SQL Server), How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" 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?

EDIT: Particularly, 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 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.

Answer 2

You could do this (ugly but it works):

INSERT INTO dbo.MyTable (ID, Name) 
select * from
(
 select 123, 'Timmy'
  union all
 select 124, 'Jonny' 
  union all
 select 125, 'Sally'
 ...
) x

Answer 3

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

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question...

Answer 4

If your data is already in your database you can do:

INSERT INTO MyTable(ID, Name)
SELECT ID, NAME FROM OtherTable

If you need to hard code the data then I think SQL 2008 let's you do the following...

INSERT INTO MyTable (Name, ID)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

Answer 5

You can use a union:

INSERT INTO dbo.MyTable (ID, Name) 
SELECT ID, Name FROM (
    SELECT 123, 'Timmy'
    UNION ALL
    SELECT 124, 'Jonny'
    UNION ALL
    SELECT 125, 'Sally'
) AS X (ID, Name)

Answer 6

This looks OK for SQL Server 2008. For SS2005 & earlier, you need to repeat the VALUES statement.

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

EDIT:: My bad. You have to repeat the 'INSERT INTO' for each row in SS2005.

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

Answer 7

Perhaps you remember doing a BULK INSERT from a text table. It may be a simplification if the data is already in a text table, but there effort in setting up the format file.

Answer 8

Corresponding to INSERT (Transact-SQL) (SQL Server 2005) you can't omit INSERT INTO dbo.Blah and have to specify it every time or use another syntax/approach,