Inserted Identities

Hi i have a Query Like this:

INSERT INTO TABLE1
SELECT * FROM TABLE2

TABLE1 has a identity column,
now i want to know what identities have been inserted into TABLE1 after the Query executes.

Be Sure,
Hosseinhi try this

INSERT
INTO Table1
SELECT *
FROM Table2

-- assuming Col1 and Col2 are your unique column identifiersa
SELECT t1.TheIdentityColumn
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Col1 = t2.Col1
t1.Col2 = t2.Col2|||

You can do with @.@.ROWCOUNT.

Code Snippet

SET NOCOUNT ON;

Insert Into <Your Identity Table>

Select <some columns> from <some table>;

Select * From <Your Identity Table>Where identity_column > Scope_Identity() - @.@.Rowcount

|||

This use of SCOPE_IDENTITY() is not guaranteed to to work. It is possible and happens that rows can be inserted into the table in the middle of the sequence. If you are using SQL Server 2005, you can use the OUTPUT clause with your INSERT statement to fetch the identity columns of the inserted rows.

Rhamille's code will work if you have the alternate keys to the table.

|||I agree with Kent point.|||Here is how you can use the OUTPUT clause:

DECLARE @.table1 TABLE
(
IDCol INT
)

INSERT INTO Table1(fldlist)
OUTPUT INSERTED.IDCol INTO @.table1(IDCol)
SELECT * FROM Table2

SELECT * FROM @.table1 will give you the identity columns that were inserted.