如何使用 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屋!