如何从SQLServer SELECT语句返回新的IDENTITY列值?

问题描述

我要插入具有自动递增键字段的SQLServer表. (我相信这在SQLServer中称为IDENTITY列.)

I'm inserting into an SQLServer table with an autoincrementing key field. (I believe this is called an IDENTITY column in SQLServer.)

在Oracle中，我可以使用RETURNING关键字为INSERT语句提供一个类似于SELECT查询的结果集，该结果集将返回生成的值:

In Oracle, I can use the RETURNING keyword to give my INSERT statement a results set like a SELECT query that will return the generated value:

INSERT INTO table
(foreign_key1, value)
VALUES
(9, 'text')
RETURNING key_field INTO :var;

如何在SQLServer中完成此操作?

How do I accomplish this in SQLServer?

奖金:好的，到目前为止，很好的答案，但是，如果可能的话，如何将其放入单个语句中? :)

Bonus: Okay, nice answers so far, but how do I put it into a single statement, if possible? :)

推荐答案

通常，它不能在单个语句中完成.

In general, it can't be done in a single statement.

但是SELECT SCOPE_IDENTITY()可以(并且应该)直接放在INSERT语句之后，因此所有操作都在同一数据库调用中完成.

But the SELECT SCOPE_IDENTITY() can (and should) be placed directly after the INSERT statement, so it's all done in the same database call.

示例:

mydb.ExecuteSql("INSERT INTO table(foreign_key1, value) VALUES(9, 'text'); SELECT SCOPE_IDENTITY();");

您可以使用OUTPUT，但是您应该注意一些限制:

You can use OUTPUT, but it has some limitations you should be aware of:

http://msdn.microsoft.com/en-us/library/ms177564.aspx

这篇关于如何从SQLServer SELECT语句返回新的IDENTITY列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！