Copy Each Identity Of Inserted Records

Question

I have two table first:

Table:     TBL#Sell

   SellId    ClientId    ProductId
                1           3
                3           5
                4            6

second:

 Table: TBL#Sell2

    SellId      ClientId       ProductId

Now I want to copy every record of first table to second one. "SellId" column in second table (Sell2.SellId) is Auto Increment (Identity).

for any insert the TBL#Sell2.SellId will set with new identity and i must store the identity in TBL#Sell1.SellId
Is it clear?
What is the solution? , plz. thanks

I want to store TBL#Sell2.SellId in TBL#Sell.SellId

Answer 1

I think you have more ways to perform this task.

You could write a stored procedure that for every record in table1 write to table2 and then update table1 getting the identity value from SCOPE_IDENTITY()

Or another way could be, if the couple ClientId/ProductId is a key, to do an insert and then an update with something like that:

insert into TBL#Sell2
select SellId, ClientId, ProductId from TBL#Sell

upadte TBL#Sell
set TBL#Sell.SellId = TBL#Sell2.SellId
from TBL#Sell T1 join TBL#Sell2 T2
on T1.ClientId = T2.ClientId and T1.ProductId  = T2.ProductId 

EDIT Replaced @@Identity with SCOPE_IDENTITY

Answer 2

You can use triggers :

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

Answer 3

Look at the OUTPUT clause in Books Online.

Answer 4

It TBL#Sell2 is empty you basically only want to give a number to each row in TBL#Sell. You can do that without using TBL#Sell2.

Add a temporary identity column, move the values to to SellId, remove the temp column.

alter table TBL#Sell add SellId_tmp int not null identity
go
update TBL#Sell set SellId = SellId_tmp
go
alter table TBL#Sell drop column SellId_tmp

Another way is to use a CTE with row_number().

;with cte as
(
  select *,
    row_number() over(order by (select(1))) as rn
  from TBL#Sell
) 
update cte set
  SellId = rn