Using @@identity or output when inserting into SQL Server view?

Question

(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

Longer:

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.

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?

Solution 1

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

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 

EDIT:

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

Solution 2

@@IDENTITY returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well

Although the issue with either of these is fixed by microsoft , I would suggest you should go with "OUTPUT", and yes, it can be used with view as well

Comments

[stahler84]

But will this work from a view instead of from a table? Imagine if you have three tables, A, B, and a cross reference AB. Your view will inner join A to B based on the cross reference of AB. If you insert into the view, and are therefore inserting into two underlying tables, will this still apply?

[stahler84]

Thanks for your help! this was very helpful as well.

[stahler84]

Thanks man! Last question, if you see this - What if you are making a view of multiple tables that are joined together (inner join)? Will output still work it this case?