Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause

Question

I have a table with two columns:

CREATE TABLE MyTable(
  Id int IDENTITY(1,1) NOT NULL,
  Name nvarchar(100) NOT NULL);

I want to duplicate the data using SELECT INSERT statement:

INSERT INTO MyTable (Name)
SELECT Name FROM MyTable

and here is the trickey part - I want to retrieve a mapping table between the original identity and the new identity:

DECLARE @idsMap TABLE (OriginalId int, NewId int)

I know I suppose to use the OUTPUT clause, but for some reason it doesn't work:

INSERT INTO MyTable (Name)
OUTPUT t.Id, INSERTED.Id INTO @idsMap (OriginalId, NewId)
SELECT Name FROM MyTable t
-- Returns error The multi-part identifier "t.Id" could not be bound.

---

Related questions:
can SQL insert using select return multiple identities?
Possible to insert with a Table Parameter, and also retrieve identity values?

Courson

Are the values in the "Name" field unique or are there any duplicates? Meaning, prior to duplicating them via this query, are they unique. Clearly they will not be unique once this INSERT runs. – Pretension

What version of SQL Server? If 2008 or later, you can use the MERGE trick ,easily found by looking at related questions on here. – Alejandraalejandrina

@srutzky, The problem here simplified for clarity. I can't be sure that the data is unique, and even if it is unique I prefer not to run another query because the indexes are not.. how can I say it gently... not optimal. – Courson

@Damien_The_Unbeliever, I'm not familiar with this MERGE you are talking about. Isn't it something resembles UPSERT? How can I apply it to this problem? – Courson

@Courson - see the top non-accepted answer on this related question. Yes, it's UPSERT. You arrange for it to always actually INSERT but you're allowed to access the source table in the OUTPUT clause, unlike here with the INSERT statement. – Alejandraalejandrina

@Damien_The_Unbeliever, thank you. The syntax is confusing at first, but it's not that complicated after I got used to it. I should really take the time to read this: sqlblog.com/blogs/adam_machanic/archive/2009/08/24/… – Courson

Does this answer your question? T-SQL Output Clause: How to access the old Identity ID – Louvar

Answer 1

It can be achieved using MERGE INTO and OUTPUT:

MERGE INTO MyTable AS tgt
USING MyTable AS src ON 1=0 --Never match
WHEN NOT MATCHED THEN
INSERT (Name)
VALUES (src.Name)
OUTPUT
    src.Id,
    inserted.Id
INTO @idsMap;

Courson

Had a similar problem and used that woraround. But it is very slow :( – Gayelord

Answer 2

How about just adding a new column to MyTable? You can keep it around as long as you need to analysis or whatever. I have to say it seems a bit off to me to create a copy of the table but that is up to you to decide.

Something like this might work for you.

alter table MyTable
add OldID int null;

INSERT INTO MyTable (Name, OldID)
SELECT Name , Id
FROM MyTable t

select * from MyTable

Nabonidus

Clean and simple, but unfortunately I can't do that. It's not really MyTable, it's a production DB table with millions of records. If I can't use OUTPUT my other option is to iterate over the query result and build the mapping one by one using SCOPE_IDENTITY, but it's a lot messier. – Courson

No don't do a loop over a million rows. Messy is not the issue, it would take hours to run. Understand about the production db. Adding a column shouldn't be an issue but I know how that can be sometimes. Lemme think on this for a minute and we will find something better than RBAR for this. – Nabonidus

I'm a bit afraid to make structural changes, even temporarily. I'm not sure if it is regulatory legal. People tend to be very strict when tons of money is involved :) – Courson