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

2022-01-09 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"附近的语法不正确.

推荐答案

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

相关文章