在不重复"INSERT INTO ..."的情况下插入多行.声明的一部分? [英] Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

问题描述

我知道我早在几年前就完成了此操作，但是我不记得该语法，而且由于收集了大量有关批量导入"的帮助文档和文章，所以在任何地方都找不到它.

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

我知道这与正确的语法 close 很近.我可能在其中需要散装"一词，或者什么，我记不起来了.有什么主意吗?

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

我得到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)...

这篇关于在不重复"INSERT INTO ..."的情况下插入多行.声明的一部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！