插入SQL Server视图时使用@@ identity还是输出? [英] Using @@identity or output when inserting into SQL Server view?

问题描述

(请原谅-我是StackOverflow和SQL的新手)Tl; dr-使用 @@ identity (或任何其他选项，例如scope_identity或输出变量)时，是否还可以使用视图?这是使用 @@ identity 的存储过程的示例:

(forgive me - I'm new to both StackOverflow & SQL) Tl;dr - When using @@identity (or any other option such as scope_identity or output variable), is it possible to also use a view? Here is an example of a stored procedure using @@identity:

--SNIP--
DECLARE @AID INT
DECLARE @BID INT


INSERT INTO dbo.A (oct1)
VALUES
(@oct1)

SELECT @AID = @@IDENTITY;

INSERT INTO dbo.B (duo1)
VALUES
(@duo2)

SELECT @BID = @@IDENTITY

INSERT INTO dbo.tblAB (AID, BID)
VALUES
(@AID, @BID)
GO

更长:

插入表中时，可以使用 @@ identity 捕获身份种子的当前值.如果要插入表A和B，捕获标识值，然后插入与A和B相关的表AB，这很有用.显然，这是出于数据标准化的目的.

When inserting into a table, you can capture the current value of the identity seed using @@identity. This is useful if you want to insert into table A and B, capture the identity value, then insert into table AB relating A to B. Obviously this is for purposes of data normalization.

比方说，您要通过一些DB Schema来抽象DB Schema，该DB Schema在表上执行内部联接，以使数据更易于使用.在这种情况下，您将如何正确填充交叉引用表?可以用相同的方式完成吗?如果可以，怎么办?

Let's say you were to abstract the DB Schema with a few that performs inner joins on your tables to make the data easier to work with. How would you populate the cross reference tables properly in that case? Can it be done the same way, if so, how?

推荐答案

如果系统使用并行计划，请避免使用@@ IDENTITY或SCOPE_IDENTITY()，因为这是一个令人讨厌的错误.请参考- http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID = 328811

Avoid using @@IDENTITY or SCOPE_IDENTITY() if your system is using Parallel plans as there is a nasty bug. Please refer - http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

获取插入的标识ID的更好方法是使用OUTPUT子句.

Better way to fetch the inserted Identity ID would be to use OUTPUT clause.

CREATE TABLE tblTest
(         
    Sno         INT IDENTITY(1,1) NOT NULL,         
    FirstName   VARCHAR(20) 
)  

DECLARE @pk TABLE (ID INT)  

INSERT INTO tblTest(FirstName) 
OUTPUT INSERTED.Sno INTO @pk
SELECT 'sample' 

SELECT * FROM @pk 

它也可以与Views一起使用.请参阅下面的示例.希望这就是您想要的.

It would work with Views as well. Please see the sample below. Hope this is what you were looking for.

CREATE VIEW v1
AS
SELECT sno, firstname FROM tbltest
GO

DECLARE @pk TABLE (ID INT)  

INSERT INTO v1(FirstName) 
OUTPUT INSERTED.Sno INTO @pk
SELECT 'sample' 

SELECT ID FROM @pk

这篇关于插入SQL Server视图时使用@@ identity还是输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！