1

I have a table which I need to copy records from back into itself. As part of that, I want to capture the new rows using an OUTPUT clause into a table variable so I can perform other opertions on the rows as well in the same process. I want each row to contain its new key and the key it was copied from. Here's a contrived example:

INSERT 
    MyTable (myText1, myText2) -- myId is an IDENTITY column
OUTPUT  
    Inserted.myId, 
    Inserted.myText1, 
    Inserted.myText2 
INTO
    -- How do I get previousId into this table variable AND the newly inserted ID? 
    @MyTable    
SELECT 
    -- MyTable.myId AS previousId,
    MyTable.myText1,
    MyTable.myText2
FROM
    MyTable
WHERE
    ...

SQL Server barks if the number of columns on the INSERT doesn't match the number of columns from the SELECT statement. Because of that, I can see how this might work if I added a column to MyTable, but that isn't an option. Previously, this was implemented with a cursor which is causing a performance bottleneck -- I'm purposely trying to avoid that.

How do I copy these records while preserving the copied row's key in a way that will achieve the highest possible performance?

Arion
  • 31,011
  • 10
  • 70
  • 88
jonathan.cone
  • 6,592
  • 2
  • 30
  • 30
  • 1
    As you've mentioned, the easiest way to do this would be with an additional column to house the old ID. What is the reason behind adding a column not being an option? – dougajmcdonald Mar 05 '12 at 16:24
  • Yeah, I think you're right, but the column doesn't make sense outside of this copy operation -- I guess I'm not completely opposed to it if nothing else pans out. – jonathan.cone Mar 05 '12 at 16:28
  • It's not ideal as you say because it's not really part of the data perminantly. I suppose you could add the column as part of the procedure and remove it afterwards but that seems a bit heavy handed. I'd be tempted to make it nullable, and ensure you null out the columns after use to save space (assuming you don't need to persist the old ID values outside of the procedure) – dougajmcdonald Mar 05 '12 at 17:00
  • 1
    If you were on SQL Server 2008 you could use `merge`. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id – Mikael Eriksson Mar 05 '12 at 17:34
  • @Mikael Eriksson Yes, I would love to be able to use MERGE. – jonathan.cone Mar 05 '12 at 20:32

1 Answers1

1

I'm a little unclear as to the context - is this in an AFTER INSERT trigger.

Anyway, I can't see any way to do this in a single call. The OUTPUT clause will only allow you to return rows that you have inserted. What I would recommend is as follows:

DECLARE @MyTable (
    myID INT, 
    previousID INT, 
    myText1 VARCHAR(20), 
    myText2 VARCHAR(20)
)

INSERT @MyTable (previousID, myText1, myText2) 
SELECT myID, myText1, myText2 FROM inserted

INSERT MyTable (myText1, myText2) 
SELECT myText1, myText2 FROM inserted

-- @@IDENTITY now points to the last identity value inserted, so...
UPDATE m SET myID = i.newID
FROM @myTable m, (SELECT @@IDENTITY - ROW_NUMBER() OVER(ORDER BY myID DESC) + 1 AS newID, myID FROM inserted) i
WHERE m.previousID = i.myID

...

Of course, you wouldn't put this into an AFTER INSERT trigger, because it will give you a recursive call, but you could do it in an INSTEAD OF INSERT trigger. I may be wrong on the recursive issue; I've always avoid the recursive call, so I've never actually found out. Using @@IDENTITY and ROW_NUMBER(), however, is a trick I've used several times in the past to do something similar.

Peter
  • 1,055
  • 6
  • 8