5

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?

Community
  • 1
  • 1
HuBeZa
  • 4,715
  • 3
  • 36
  • 58
  • 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. – Solomon Rutzky Jul 10 '14 at 14:47
  • What version of SQL Server? If 2008 or later, you can use the `MERGE` trick ,easily found by looking at related questions on here. – Damien_The_Unbeliever Jul 10 '14 at 14:49
  • @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. – HuBeZa Jul 10 '14 at 14:58
  • @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? – HuBeZa Jul 10 '14 at 15:03
  • @HuBeZa - see the top non-accepted answer on this [related question](http://stackoverflow.com/questions/1766335/t-sql-output-clause-how-to-access-the-old-identity-id?rq=1). 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. – Damien_The_Unbeliever Jul 10 '14 at 15:58
  • @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: http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx – HuBeZa Jul 14 '14 at 07:25

2 Answers2

5

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;
HuBeZa
  • 4,715
  • 3
  • 36
  • 58
1

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
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • 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. – HuBeZa Jul 10 '14 at 14:52
  • 1
    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. – Sean Lange Jul 10 '14 at 14:55
  • 1
    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 :) – HuBeZa Jul 10 '14 at 15:06