10

I have a table in which I need to copy certain rows. I can get IDs of new rows like this:

DECLARE @IDs TABLE (ID int)
INSERT T (name, address)
OUTPUT INSERTED.TID INTO @ids
    SELECT name, address
    FROM T

But what I would like to have is something like this:

DECLARE @IDs TABLE (oldID int, newID int)
INSERT T (name, address)
OUTPUT T.ID, INSERTED.TID INTO @ids
    SELECT name, address
    FROM T

Can this be done with SQL Server?

P.S. I'm not doing this programmaticaly, because it has to be done by a stored procedure.

gligoran
  • 3,267
  • 3
  • 32
  • 47
  • 2
    possible duplicate of [How to copy tables avoiding cursors in SQL?](http://stackoverflow.com/questions/6174355/how-to-copy-tables-avoiding-cursors-in-sql) – Andriy M Jan 07 '12 at 23:50
  • 1
    Basically, explore [this question](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id "Using merge..output to get mapping between source.id and target.id")'s *Linked* section: a lot of questions link to it, and many are similar to yours. – Andriy M Jan 07 '12 at 23:53
  • 2
    This doesn't make much sense. There would be no "old" `ID` for an inserted row. Are you talking about updates? –  Jan 07 '12 at 23:57
  • 1
    @Shark: I'm not taking about UPDATEs. What I was trying to do is duplicate certain rows in a table and as a result get pairs of IDs that show which old/existing row was duplicated into which new row. – gligoran Jan 08 '12 at 00:55

1 Answers1

12

With helpful links from Andriy M's link to 'How to copy tables avoiding cursors in SQL?', I managed to come up with this very elegant solution:

DECLARE @t TABLE (oID int, nID int);

MERGE T s
USING (
        SELECT TID, name, address
        FROM T [s]
      ) d on 0 = 1
WHEN NOT MATCHED
THEN INSERT (name, address)
    VALUES (name, address)
OUTPUT d.TID as oID, Inserted.TID as nID
INTO @t;
Community
  • 1
  • 1
gligoran
  • 3,267
  • 3
  • 32
  • 47
  • 1
    Not sure why this isn't scored higher, it's very handy, thanks. Would be easier to read with more descriptive var names. :) `T s` could be `MyTable tblTo`, `T [s]` just `MyTable`, and `d` could be `tblFrom`. – ingredient_15939 Sep 06 '17 at 15:16
  • Thanks, that was exactly what I was looking for but couldn't find anywhere. It's sad SQL Server can't accept SELECT on INSERT VALUES. – Mateus Felipe Sep 24 '19 at 17:36