May 21, 2007 at 1:52 am
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
May 21, 2007 at 3:25 am
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
May 21, 2007 at 4:49 am
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.
May 21, 2007 at 5:36 am
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
May 21, 2007 at 5:45 am
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?
May 21, 2007 at 6:17 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply