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.

Insert Relative Columns

So I want to insert 40 values into a table, starting at a particular column. Like this:

INSERT INTO MyTable (1) VALUES (...my forty values...)

Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).

The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:

INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)

But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?

It is best practice to name all of the columns, even if you weren't using identities. One of the most heinous problems an application I have had to work with was that they didn't name columns, then replication needed to add a column and boom, all of the code had to be rewritten.

It is easy to write though, just right click the table in SSMS or QA and say script table as insert... This will enerate a script for you that has all of the columns (without the identity column and timestamp if you have one.) It wouldn't be too hard to fashion one out of a query to information_schema.columns also.

If you feel strongly, I would suggest that you go here and submit this feedback: http://connect.microsoft.com/SQLServer/Feedback. It would be nice in ad-hoc usage to not have to name the columns, though