Copying multiple row and it's related rows in SQL
Does anyone know a simple开发者_StackOverflow中文版 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?
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
精彩评论