December 9, 2010 at 3:32 pm
I have table dogs which is having a new row inserted where the key is an identity column
I then want to grab that identity into a variable and insert it into a second table - and i need scope_Identity i think.
now sure just how.
Can someone help with an example?
December 9, 2010 at 3:49 pm
Just drop it to a variable and use it from there:
CREATE TABLE #tmp ( tID INT IDENTITY( 1, 1), tValue VARCHAR(10))
Declare @LastID INT
INSERT INTO #tmp (tValue) VALUES ( 'Oscar')
SET @LastID = SCOPE_IDENTITY()
PRINT @LastID
INSERT INTO #tmp (tValue) VALUES ( 'Big Bird')
SET @LastID = SCOPE_IDENTITY()
PRINT @LastID
INSERT INTO #tmp (tValue) VALUES ( 'Elmo')
SET @LastID = SCOPE_IDENTITY()
PRINT @LastID
INSERT INTO #tmp (tValue) VALUES ( 'Grover')
SET @LastID = SCOPE_IDENTITY()
PRINT @LastID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 5:30 pm
Thank you, I kind of see what you are doing..but in the table insert that requires the identity is like this
INSERT INTO dog_name
(
dog_ID --Identity from previous table insert
dog_name
)
SELECT identity, dog_name
FROM dogs
How do I get the identity into that type of select format?
December 9, 2010 at 5:45 pm
vbradham (12/9/2010)
Thank you, I kind of see what you are doing..but in the table insert that requires the identity is like thisINSERT INTO dog_name
(
dog_ID --Identity from previous table insert
dog_name
)
SELECT identity, dog_name
FROM dogs
How do I get the identity into that type of select format?
Ah, that's a whooole different problem. SCOPE_IDENTITY and @@IDENTITY only relate to the last increment, not the entirity.
In 2k5+ you could use the OUTPUT clause and trap the generated identities. In 2k, this requires a lot of workarounds.
A trick I have used before is to have a non-used column that's a GUID just trailing on the table. When you do a bulk insert like this, you generate said GUID and apply it to all rows you just inserted. Then you can re-select them back out to deal with the generated IDs.
Other methods include using a counter table and basically grabbing your own block of ids so you know what you're working with, to using cursors so that you loop through and grab SCOPE_IDENTITY() on a per insert basis.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply