June 16, 2009 at 1:49 pm
I am using @@IDENTITY to determine the ID of the last inserted value in a table (Table1). I use this as a foreign key and insert a record in a second table (Table2) in certain situations. I am also trying to use an output clause for logging (LogTable).
@@IDENTITY is picking up the ID from the record inserted in the log table instead of Table1--because it is inserted last. Is there a way around this. I need a way of getting the last inserted identity of Table1. My code looks like this:
DECLARE @ID INT
INSERT INTO Table1 ( Col1, Col2 )
OUTPUT INSERTED.ID
,INSERTED.Col1
,INSERTED.Col2
INTO dbo.LogTable
VALUES (
@Col1Value
,@Col2Value
) ;
SET @ID = @@IDENTITY ;
IF @Col2Value IS NOT NULL
BEGIN
INSERT INTO Table2 ( ID, Col2 )
VALUES ( @ID, @Col2Value ) ;
END
June 16, 2009 at 2:11 pm
First, switch to Scope_Identity instead of @@Identity. It's a better option.
Second, move the assignment to right after the insert, instead of after the second insert. Or am I missing something?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 2:24 pm
The assignment is after the first insert, but the OUTPUT clause runs after the INSERT statement and overrides the @@IDENTITY value.
June 16, 2009 at 3:02 pm
Are you using Output Into, or just getting an on-screen output of the dataset?
If you're using Output Into, then select the value from the table you input into. If you need an on-screen dataset, use Output Into, and then select the resulting dataset from the target table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2009 at 3:52 pm
I figured it out. @@IDENTITY will not work. This is what I needed to do:
SET @ID = IDENT_CURRENT('Table1 ')
June 16, 2009 at 8:59 pm
southwynd (6/16/2009)
I figured it out. @@IDENTITY will not work. This is what I needed to do:SET @ID = IDENT_CURRENT('Table1 ')
No... IDENT_CURRENT can allow other people "in". Take the first suggestion... use SCOPE_IDENTITY. Look both of those up in Books Online to see why.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply