在 INSERT 之后使用 OUTPUT 将标识列的值放入(非表值)变量中 [英] Using OUTPUT after INSERT to get value of identity column into a (non-table value) variable

问题描述

给定以下简单测试表:

 <代码> CREATE TABLE dbo.Test(ID BIGINT IDENTITY(1,1)NOT NULL，名称VARCHAR(50)NULL)

我想获得标识列的值成一个标量变量的<代码> INSERT 使用输出子句后，但这并不工作:

<预> <代码> DECLARE @InsertedId BIGINT;INSERT INTO测试(名称)OUTPUT @ InsertedId = inserted.IdVALUES( '迈克尔')- 显示所得ID用于调试SELECT @InsertedId;- ...

我知道可以很容易地做到这一点使用<代码> SCOPE_IDENTITY()在后插入，但有可能做到这一点作为<代码> INSERT 使用输出子句不诉诸表变量声明?

<强>更新下，另一人为尝试，这也是不合法的:

 <代码>  - 返回的ID作为结果集INSERT INTO测试(名称)OUTPUT inserted.Id AS TheIdVALUES( '迈克尔')- 但你不能用结果集作为派生表...SELECT TheId FROM(INSERT INTO测试(名称)OUTPUT inserted.Id AS TheIdVALUES( '迈克尔'))-  ...，或者你就可以做到这一点SELECT TOP 1 @ InsertedId = TheId从(INSERT INTO测试(名称)OUTPUT inserted.Id AS TheIdVALUES( '迈克尔'))

解决方案

记住输出条款的价值在于，它可以返回多个记录和多个字段.这样你就可以同时输出的自然键和一组数据，所以你也可以使用集理论的多条记录插入到子表的身份.输出是非常强大的，它会支付给习惯使用它.

有目前在SCOPE_IDENTITY(错误)(参见链接: http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value )，微软不打算修复.这应该给你一个线索，你是否应该使用新开发输出，即使它是一个记录的位cludgier.

Given the following simple test table:

CREATE TABLE dbo.Test
(
  Id bigint IDENTITY(1,1) NOT NULL,
  Name varchar(50) NULL
)

I would like to get the value of the identity column into a scalar variable after the INSERT using the OUTPUT clause, but this does not work:

DECLARE @InsertedId BIGINT;

INSERT INTO Test(Name) 
  OUTPUT @InsertedId=inserted.Id
  VALUES ('Michael')

-- Display resulting id for debugging
SELECT @InsertedId;
-- ...

I know I can easily do this using SCOPE_IDENTITY() after the INSERT, but is it possible to do it as part of the INSERT statement using the OUTPUT clause without resorting to a table variable?

Update, another contrived attempt that is also not legal:

-- Return the id as a result set
INSERT INTO Test(Name) 
OUTPUT inserted.Id AS TheId
VALUES ('Michael')

-- But you can't use the result set as a derived table...
SELECT TheId FROM
(
  INSERT INTO Test(Name) 
  OUTPUT inserted.Id AS TheId
  VALUES ('Michael')
)

-- ..., or you would be able to do this
SELECT TOP 1 @InsertedId=TheId
FROM
(
  INSERT INTO Test(Name) 
  OUTPUT inserted.Id AS TheId
  VALUES ('Michael')
)

解决方案

Remember the value of the output clause is that it can return more than one record and more than one field. So you can output both the natural key and the identity for a set of data so you can also use set theory to insert multiple records into child tables. Output is very powerful and it will pay to get used to using it.

There currently is a bug in scope_identity() (see link: http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value) that Microsoft does not intend to fix. That should give you a clue as to whether you should be using output for new development even if it is a bit cludgier for single records.

这篇关于在 INSERT 之后使用 OUTPUT 将标识列的值放入(非表值)变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！