SQL Server Performance Forum – Threads Archive

Getting Identity Values for group of inserts

Hi all, I am using the following sql statements to select from a table and insert into destination table. SELECT * INTO TEMP_TABLE
FROM TABLE1 How can I get the identity values for all the records that are inserted. Thanks and Regards,
Ram Kumar N D

SELECT identitycol
FROM TEMP_TABLE
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. http://www.sqlpass.de)

The temp table is already having some rows. so i need to get the identity values for the latest select query thet i had executed

After insert do this:
select @@identity
this will give the last inserted identity value.

Determine the MAX(identitycol) before doing the SELECT INTO. All values greater than that, you have inserted vai SELECT INTO. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

If you run this SELECT * INTO TEMP_TABLE
FROM TABLE1 then the new table TEMP_TABLE is created with the values in TABLE1 and values are not appended. Then how do you expect it having previous data? To have previous data, you should have run Insert into TEMP_TABLE(columns)
SELECT columns FROM TABLE1 Then do what Frank suggested
Madhivanan Failing to plan is Planning to fail

Yes, nice catch Madhivanan.<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>