使用 INSERT INTO (SQL Server 2005) 插入多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2462517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert multiple values using INSERT INTO (SQL Server 2005)
提问by Ben McCormack
In SQL Server 2005, I'm trying to figure out why I'm not able to insert multiple fields into a table. The following query, which inserts one record, works fine:
在 SQL Server 2005 中，我试图弄清楚为什么我无法将多个字段插入到一个表中。以下查询插入一条记录，工作正常：
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test')
However, the following query, which specifies more than one value, fails:
但是，指定多个值的以下查询失败：
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test'),(1001,N'test2')
I get this message:
我收到这条消息：
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.
When I looked up the help for INSERT in SQL Sever Management Studio, one of their examples showed using the "Values" syntax that I used (with groups of values in parentheses and separated by commas). The help documentation I found in SQL Server Management Studio looks like it's for SQL Server 2008, so perhaps that's the reason that the insert doesn't work. Either way, I can't figure out why it won't work.
当我在 SQL Sever Management Studio 中查找 INSERT 的帮助时，他们的一个示例显示了使用我使用的“值”语法（括号中的值组并用逗号分隔）。我在 SQL Server Management Studio 中找到的帮助文档看起来像是针对 SQL Server 2008，所以也许这就是插入不起作用的原因。无论哪种方式，我都无法弄清楚为什么它不起作用。
回答by Oded
The syntax you are using is new to SQL Server 2008:
您使用的语法是 SQL Server 2008 的新语法：
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test'),(1001,N'test2')
For SQL Server 2005, you will have to use multiple INSERTstatements:
对于 SQL Server 2005，您将不得不使用多个INSERT语句：
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test')
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1001,N'test2')
One other option is to use UNION ALL:
另一种选择是使用UNION ALL：
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'
回答by Ashish Gupta
回答by Rae Lee
In SQL Server 2008,2012,2014 you can insert multiple rows using a single SQL INSERT statement.
在 SQL Server 2008、2012、2014 中，您可以使用单个 SQL INSERT 语句插入多行。
INSERT INTO TableName ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )
Another way
其它的办法
INSERT INTO TableName (Column1, Column2 )
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
