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,

    Hossein

  • Hossein,

    The insert will only be allowed if this setting is set:

    SET IDENTITY_INSERT table1 ON

    and with explicit columns:

    INSERT INTO table1 (col1, col2)

    SELECT col1, col2 FROM table2

    GO

    SET IDENTITY_INSERT OFF

    In this scenario the identities are kept and the identity property of the table also. In SELECT * INTO table1 FROM table2 the identity values are kept, but it is no longer an identity type in table1, just an int.

    Jan

  • Thanks jan,

    but it seems i haven't explained my problem right, so let me explain again.

    my TABLE1 has 3 fields F1,F2,F3 . witch the F1 column is an identity field.

    my TABLE2 has 2 fields F2,F3.

    now i try this query:

    INSERT INTO TABLE1

    (F2,F3)

    SELECT F2,F3 FROM TABLE2

    it works and rows from TABLE2 is inserted into TABLE1 but with WHAT F1 value??? i want to get these F1 values that is created during the insert.

  • In that case, the identity will just increment as it should. Mind that there is no specific order in Table2 and I am not sure that an ORDER BY will garantir this.

    Jan

  • thanks again jan but i think i found a way myself. im going to say it so i can have your opinion too.

    step1 - i will get the last inserted identity of the table.

    step2 - i run the query for insert.

    setp3 - i get the last inserted identity of the table again

    step4 - numbers between these two is the identities that have been inserted!

    what do you think about it?

  • Select Scope_Identity() will return the value of the last row inserted in that table on the current spid. SQL 7 users have to use @@Identity. I've got an article posted that talks about some of the details around both.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply