在“Insert Into TABLE Values()”语句中如何处理身份列？

Question

在 SQL Server 2000 或更高版本中，是否有办法处理使用类似以下语句时的自动生成的主键（identity）列？

Insert Into TableName Values(?, ?, ?)

我的目标是完全不使用列名。

- James McMahon

---

为什么你不想使用列名清单？ - Mitch Wheat

@Mitch，这有点复杂，但我有一个程序可以动态生成SQL语句，在我的原始设计中，我没有处理任何标识列，因此我排除了列名。我需要快速更新，如果我可以避免使用列名，那将使它更容易。 - James McMahon

@nemo-- 之前我也遇到过“快速修补”情况，现在我会容忍你。但是，请为了以后着想，在下一次完整更新时计划指定列名。 - RolandTumble

Answer 1

默认情况下，如果您有一个自增列，您在 VALUES 部分无需指定它。如果您的表格为：

ID    NAME    ADDRESS

然后你可以执行：

INSERT INTO MyTbl VALUES ('Joe', '123 State Street, Boston, MA')

这将自动生成ID，您无需再考虑它。如果您执行SET IDENTITY_INSERT MyTbl ON，则可以为ID列分配一个值。

- Eric

---

哦，真的吗？太棒了。我刚才还以为那行不通呢。让我试一下。 - James McMahon

Eric，这对我完全没用。它抱怨我关闭了identity insert，我猜是因为它试图将values中的第一项插入到ID中。你需要设置什么特定的东西来忽略第一列吗？ - James McMahon

@Eric，我没有指定任何列。我在一个有5列的表上运行了INSERT INTO MyTbl VALUES ('TEST1'，'TEST2'，'TEST3'，'TEST4')，其中第一列是自动生成的ID列。数据库试图将'TEST1'放入ID列中。 - James McMahon

我认为我的问题在于当我在SQL服务器查询窗口中运行查询时，它会将其转换为INSERT INTO（ColumnName，ColumnName ...等），有没有什么方法可以避免这种情况？ - James McMahon

这个是有效的，我只是遇到了一些 SQL Server GUI 的奇怪问题。 - James McMahon

Answer 2

生成列列表的另一种“技巧”是将“列”节点从对象资源管理器拖动到查询窗口中。

- Aaron Bertrand

Answer 3

最佳实践是明确列出列名：

Insert Into TableName(col1, col2,col2) Values(?, ?, ?)

否则，如果你向表中添加了另一列，你原来的插入语句将会失效。

- A-K

---

是的，我的老板也说了同样的话。你们两个都是完全正确的，但是这个数据库中的数据是不允许的（即每天被清除和重新生成），所以如果发生这样的事情，也不会是世界末日。 - James McMahon

我认为如果添加另一列时原始插入语句出现错误会更好——这可以确保插入语句适用于所有字段（尽管也可以使用字段上的NOT NULL设置来强制输入）。 - Shiraz

Answer 4

你有两个选择：

1）指定列名列表（不包括标识列）。

2）执行SET IDENTITY_INSERT tablename ON命令，然后插入提供标识列显式值的插入语句，最后执行SET IDENTITY_INSERT tablename OFF命令。

如果你想避免使用列名列表，也许这个“技巧”可以帮助你：

-- Get a comma separated list of a table's column names
SELECT STUFF(
(SELECT 
',' + COLUMN_NAME AS [text()]
FROM 
INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME = 'TableName'
Order By Ordinal_position
FOR XML PATH('')
), 1,1, '')

- Mitch Wheat

---

只是为了澄清，您所说的显式值是指您必须查询数据库以查看序列中下一个标识将是什么，并手动插入它吗？ - James McMahon

不，您可以使用任何尚不存在的值，但下一个序列将从最大值加一开始。 - Mitch Wheat

它通常只在需要在不同的应用程序安装中具有完全相同ID值的参考数据等情况下使用。 - Mitch Wheat

永远不要在生产环境中使用 Set identity_insert！除非你想让其他用户插入表时出现问题。这仅用于在单用户模式下从一个系统传输大量数据时使用。 - HLGEM

@HLGEM：我并不建议你让它保持开启！但你说得对，理想情况下应在该表上没有任何活动时执行。我们主要在参考数据表上使用它，在正常使用过程中永远不会有插入操作。 - Mitch Wheat

Answer 5

由于在评论中放置代码并不切实际，回应您在Eric答案中的评论，表示该方法对您无效...

我刚在一个SQL 2005盒子上运行了以下代码（抱歉，没有2000），使用默认设置，没有出现错误：

CREATE TABLE dbo.Test_Identity_Insert
(
    id  INT IDENTITY NOT NULL,
    my_string   VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Test_Identity_Insert PRIMARY KEY CLUSTERED (id)
)
GO

INSERT INTO dbo.Test_Identity_Insert VALUES ('test')
GO

SELECT * FROM dbo.Test_Identity_Insert
GO

你是否在值列表中发送了ID值？如果您实际上传递了该值，我认为您无法使其忽略该列。例如，如果您的表具有6个列，并且要忽略IDENTITY列，则只能传递5个值。

- Tom H

Answer 6

set identity_insert customer on
insert into Customer(id,Name,city,Salary) values(8,'bcd','Amritsar',1234)

'customer' 是表名

- sohan yadav