在SQL Server 2008中获取新插入的行的主键 [英] Getting the primary key of an newly inserted row in SQL Server 2008

问题描述

我有一堆要插入表中的数据。问题是我需要它来将主键返回到该表。我不确定是否存在以下情况：

I have a bunch of data which will insert into a table. This issue is that I need it to return the primary key to that table. I wasn't sure if there was things like:

 insert into TABLE (...) values (...) RETURNING p_key

或

 select p_key from (insert into TABLE (...) values (...))

我正在为浏览器制定一种变通办法，并保存了将或多或少添加一行然后更新它的信息……但是如果没有主键，由于没有引用，因此无法对其进行更新。

I am making a workaround for a browser and saved information which will more or less add a row and then update it... but without the primary key, there is no way to update it as there is no reference to it.

我在网上寻找并通过Google找到了一些示例，但这些示例使我有些困惑。

I was looking online and found some examples via google, but it confused me slightly with these examples.

http://en.wikipedia.org/wiki/Insert_(SQL)#Retrieving_the_key

http://www.daniweb.com/web-development/databases/ms-sql/threads/299356/returning-i最后插入行唯一身份的身份

维基百科说，对于SQL Server 2008，使用 OUTPUT 代替 RETURNING ，可以使用 OUTPUT p_key

Wikipedia was saying that for SQL Server 2008 to use OUTPUT instead of RETURNING, possible to use something like OUTPUT p_key

推荐答案

如果要插入一整套行，则选择 SCOPE_IDENTITY（）不会。而且 SCOPE_IDENTITY 也仅适用于（数字）身份列-有时您的PK是其他东西...

If you're inserting a whole set of rows, selecting the SCOPE_IDENTITY() won't do. And SCOPE_IDENTITY also only works for (numeric) identity columns - sometimes your PK is something else...

但是SQL Server 确实具有 OUTPUT 子句-，并且在MSDN上有很好的记录！

But SQL Server does have the OUTPUT clause - and it's very well documented on MSDN!

INSERT INTO dbo.Table(columns)
OUTPUT INSERTED.p_key, INSERTED.someothercolumnhere .......
VALUES(...) 

这些值将回显回调用应用程序，例如您将在SQL Server Management Studio的网格中看到它们，也可以从C＃或VB.NET调用此 INSERT 语句的结果集中读取它们。

Those values will be "echoed" back to the calling app, e.g. you'll see them in a grid in SQL Server Management Studio, or you can read them as a result set from your C# or VB.NET calling this INSERT statement.

这篇关于在SQL Server 2008中获取新插入的行的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！