sql-server - 如何在不重复声明的“INSERT INTO dbo.Blah”部分的情况下插入多行？(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.

(我可能在那里需要“BULK”这个词，或者其他东西，我不记得了。)

Any idea?

(任何的想法？)

I need this for a SQL Server 2005 database.

(我需要这个SQL Server 2005数据库。)

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'.

(我Incorrect syntax near the keyword 'VALUES'.获得了Incorrect syntax near the keyword 'VALUES'.)

  ask by Timothy Khouri translate from so

Answer 1

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

(您的语法几乎适用于SQL Server 2008（但不适用于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.

(¹当问题得到解答时，问题并不明确是指SQL Server 2005.我在这里留下这个答案，因为我认为它仍然是相关的。)