I have a 2table like
create table test1
create table test2
Existing record in test1 table
Now i want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table
i.e. after insert test1 table will be
and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.
If the repeat the process again test2 table will be like
and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.
Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..
I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.
I appreciate your responses.