I've got a load of tables where I need to clone subsets of data from. I've done this recursively using a number of loops which does complete the job, but is very slow.
The problem is that the hierarchy on the tables is structured using INT IDENTITY(1,1) columns which means I have to keep mapping old Id's to new Id's etc.
I want to be able to bulk clone each stage and do this mapping using the OUTPUT statement but I can't achieve this because I don't have access to the FROM table in the OUTPUT statement. The below example shows what I want to achieve but doesn't work.
CREATE TABLE #NewIds (IdNew INT, IdOld INT)
INSERT INTO IdTest
OUTPUT inserted.Id, i.Id
INTO #NewIds
SELECT id
FROM IdTest i
WHERE id IN (SELECT Id FROM #IdTestsToClone)
Can anyone explain to me how to get this working?
