插入多行而不重复“INSERT INTO ..."；声明的一部分?

2021-12-02 00:00:00 sql-server-2005 insert tsql sql-server

我知道我几年前就做过这个，但我不记得语法，而且我在任何地方都找不到它，因为要查阅大量关于批量导入"的帮助文档和文章.

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

我知道这是接近正确的语法.我可能在那里需要BULK"这个词，或者什么，我不记得了.有什么想法吗?

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?

我需要这个用于 SQL Server 2005 数据库.我试过这段代码，但无济于事:

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

我在关键字VALUES"附近发现语法不正确.

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

推荐答案

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

对于 SQL Server 2008，可以按照问题中的语句在一个 VALUES 子句中完全做到这一点(您只需要添加一个逗号来分隔每个值语句)...

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

相关文章