Capturing the identity field on assignment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Capturing the identity field on assignment

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2007-05-22 : 12:10:43

Hey there. I'm wondering if there's a way to capture the identity fields as they are assigned by the SQL Server. Do I need to take do a before/after compare on the table or is there some way to get the insert query to output these fields?

Thanks!

Example:
-- I'd like to have a list of the 'COUNTER' integers added by the
-- INSERT INTO #STUFF query.:

CREATE TABLE #STUFF2 ([STUFF] VARCHAR(128))
CREATE TABLE #STUFF (COUNTER INT IDENTITY, [STUFF] VARCHAR(128))

INSERT INTO #STUFF2
VALUES ('ONE')

INSERT INTO #STUFF2
VALUES ('TWO')

SELECT * FROM #STUFF2

INSERT INTO #STUFF
([STUFF])
SELECT * FROM #STUFF2

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 12:12:33

Make use of SCOPE_IDENTITY() function to retrieve last inserted identity value.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-22 : 12:12:37

you can get the inserted identity with
scope_identity()
function

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-22 : 12:13:02