Insert into dest and update source

Question

In SQL Server 2008, is it possible to insert data from a source table into a dest table and update the source table with @@identity from the dest table at the same time?

Example

Table Source:

Id - UniqueId
Name - varchar(10) 
RealId [null] - int 

Table Dest

Id - [id] INT identity
Name - varchar(10)  

I want to transfer rows from Source into Dest, and update the RealId to the @@identity value from the Dest table

I can modify Source in any way I like, the Dest table may not be altered.

What are my best options here?

Also note, the "Name" column may contain duplicates so I can't really join on that.

(The real tables are much more complex, but this should give and idea of what I want)

Answer 1

Have a look at this question. Using merge..output to get mapping between source.id and target.id . You can use output from merge to get a table variable with a link between id's in source and target. Then you can use that table to update your source table.