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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy