1

Does anyone know a simple way of copying multiple table rows and it's related items?

In my simplified example I have two tables, one with a table for companies and one for their employees and I'd like to be able to clone entire companies with all their employees in a simple way.

[Companies]

- ID (Auto-increment)
- Name

[Employees]

- ID
- CompanyID (Foreign Key)
- Name

I've been playing around with the OUTPUT-clause but just managed to return the inserted IDs without a reference to the original row.

I just can't figure out how to link a inserted row to the original one?

If the insert-statement returned a table like this:

@mappingTable

-InsertedCompanyID
-OriginalCompanyID

I could run a statement like this to copy all employees:

INSERT INTO Employees (CompanyID, Name)
SELECT m.InsertedCompanyID, x.Name FROM @mappingTable m 
INNER JOIN Employees x (x.CompanyID = m.OriginalCompanyID)

Or am I on the wrong path? Is there's a better way to accomplish this?

Jonas Stensved
  • 14,378
  • 5
  • 51
  • 80
  • 2
    If you are on SQL Server 2008 you can use the output clause with `merge`. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id – Mikael Eriksson Aug 29 '11 at 09:11
  • Exactly what i was looking for. Thank you! Make it an answer and I'll mark it as accepted :) – Jonas Stensved Aug 29 '11 at 09:18

1 Answers1

1

You can use the output clause of the merge statment to map between the old ID and the new auto incremented ID.

Have a look at this question: Using merge..output to get mapping between source.id and target.id

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