5

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Roger Johansson
  • 22,764
  • 18
  • 97
  • 193
  • 1
    First comment: use `SCOPE_IDENTITY()` instead of `@@Identity` - it's safer and more true to what you're really looking for. Second: if you want to insert into dest and then update source, you need to have **some** field that is the same in both - if `name` can't do it, then you need something else, e.g. `SourceId` on the `Dest` table or something. – marc_s Apr 01 '11 at 15:20

1 Answers1

4

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.

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281