使用 INSERT INTO (SQL Server 2005) 插入多个值 [英] Insert multiple values using INSERT INTO (SQL Server 2005)
问题描述
在 SQL Server 2005 中,我试图弄清楚为什么我无法将多个字段插入到一个表中.以下查询插入一条记录,工作正常:
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:
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')
我收到这条消息:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.
当我在 SQL Sever Management Studio 中查找 INSERT 的帮助时,他们的一个示例显示了使用我使用的值"语法(括号中的值组并用逗号分隔).我在 SQL Server Management Studio 中找到的帮助文档看起来像是针对 SQL Server 2008,所以也许这就是插入不起作用的原因.无论哪种方式,我都无法弄清楚为什么它不起作用.
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 Server 2008 的新语法:
The syntax you are using is new to SQL Server 2008:
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test'),(1001,N'test2')
对于 SQL Server 2005,您将不得不使用多个 INSERT
语句:
For SQL Server 2005, you will have to use multiple INSERT
statements:
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test')
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1001,N'test2')
另一种选择是使用UNION ALL
:
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'
这篇关于使用 INSERT INTO (SQL Server 2005) 插入多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!