如何使用INSERT语句的OUTPUT子句获取标识值? [英] How do I use an INSERT statement's OUTPUT clause to get the identity value?

问题描述

如果我有一个插入语句，例如:

If I have an insert statement such as:

INSERT INTO MyTable
(  
  Name,
  Address,
  PhoneNo
)
VALUES
(
  'Yatrix',
   '1234 Address Stuff',
   '1112223333'
)

如何使用OUTPUT子句将@var INT设置为新行的标识值(称为Id)?例如，我已经看到了将INSERTED.Name放入表变量中的示例，但是我无法将其放入非表变量中.

How do I set @var INT to the new row's identity value (called Id) using the OUTPUT clause? I've seen samples of putting INSERTED.Name into table variables, for example, but I can't get it into a non-table variable.

我尝试过OUPUT INSERTED.Id AS @var，SET @var = INSERTED.Id，但是都没有用.

I've tried OUPUT INSERTED.Id AS @var, SET @var = INSERTED.Id, but neither have worked.

推荐答案

您可以将新插入的ID输出到SSMS控制台，如下所示:

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

您也可以在例如C#，当您需要将ID返回给调用应用程序时-只需使用.ExecuteScalar()(而不是.ExecuteNonQuery())执行SQL查询，以读取返回的ID.

You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar() (instead of .ExecuteNonQuery()) to read the resulting ID back.

或者，如果您需要在T-SQL中捕获新插入的ID(例如，供以后进行进一步处理)，则需要创建一个表变量:

Or if you need to capture the newly inserted ID inside T-SQL (e.g. for later further processing), you need to create a table variable:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

这样，您可以将多个值放入@OutputTbl并对其进行进一步处理.您还可以在此处使用常规"临时表(#temp)或什至真实"持久表作为您的输出目标".

This way, you can put multiple values into @OutputTbl and do further processing on those. You could also use a "regular" temporary table (#temp) or even a "real" persistent table as your "output target" here.

这篇关于如何使用INSERT语句的OUTPUT子句获取标识值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！