Can I keep old keys linked to new keys when making a copy in SQL?

Question

I am trying to copy a record in a table and change a few values with a stored procedure in SQL Server 2005. This is simple, but I also need to copy relationships in other tables with the new primary keys. As this proc is being used to batch copy records, I've found it difficult to store some relationship between old keys and new keys. Right now, I am grabbing new keys from the batch insert using OUTPUT INTO. ex:

INSERT INTO table
 (column1, column2,...)
 OUTPUT INSERTED.PrimaryKey INTO @TableVariable
 SELECT column1, column2,...

Is there a way like this to easily get the old keys inserted at the same time I am inserting new keys (to ensure I have paired up the proper corresponding keys)?

I know cursors are an option, but I have never used them and have only heard them referenced in a horror story fashion. I'd much prefer to use OUTPUT INTO, or something like it.

You can't have parent and child keys inserted at the same time. The parent keys have to be inserted first. — Dan Bracuk, May 02 '13 at 01:31
If I understand correctly, you're saying I can't insert the relations at the same time as the main record. I don't mean to insert them into the relation right away. I mean to also insert the Primary key of the record I'm copying from into @TableVariable in the same record as the primary key I'm copying to. — Garrett Daniel DeMeyer, May 02 '13 at 04:21
I've assumed (given the presence of `OUTPUT` and `INSERTED`) that this is for SQL Server, and given an answer based on that. It would be much better to not have to guess though - could you add an appropriate tag to your question? (`sql` by itself is a tag about the SQL language - implemented by many different RDBMS products, in various different ways) — Damien_The_Unbeliever, May 02 '13 at 06:27
@Damien_The_Unbeliever Thanks for the tip for question asking. It is helpful when someone notices I'm new and points out how to get better results. — Garrett Daniel DeMeyer, May 02 '13 at 14:17
May I assume (1) that "INSERTED.PrimaryKey" is the new key, and (2) that the old key is available at the time of the insert? If so, then why not have an "OldPK" field in the target table, and insert the old key right then and there? Once you're completely satisfied you no longer need it, you can always null out OldPK and alter the table to remove later on. — johnjps111, Jul 20 '15 at 19:40

score 4 · Answer 1 · edited May 23 '17 at 12:32

4

If you need to track both old and new keys in your temp table, you need to cheat and use MERGE:

Data setup:

create table T (
    ID int IDENTITY(5,7) not null,
    Col1 varchar(10) not null
);
go
insert into T (Col1) values ('abc'),('def');

And the replacement for your INSERT statement:

declare @TV table (
    Old_ID int not null,
    New_ID int not null
);
merge into T t1
using (select ID,Col1 from T) t2
on 1 = 0
when not matched then insert (Col1) values (t2.Col1)
output t2.ID,inserted.ID into @TV;

And (actually needs to be in the same batch so that you can access the table variable):

select * from T;
select * from @TV;

Produces:

ID  Col1
5   abc
12  def
19  abc
26  def

Old_ID  New_ID
5       19
12      26

The reason you have to do this is because of an irritating limitation on the OUTPUT clause when used with INSERT - you can only access the inserted table, not any of the tables that might be part of a SELECT.

Related - More explanation of the MERGE abuse

edited May 23 '17 at 12:32

Community

1
1

answered May 02 '13 at 06:24

Damien_The_Unbeliever

234,701
27
340
448

Thank you for your help. With your advice, I added that I am using SQL Server 2005 to the question and merge is not implemented in 2005. – Garrett Daniel DeMeyer May 02 '13 at 14:38
Well, we're out of luck then. Generally, you would use whatever combination of non-Identity columns can also be used to uniquely identify rows - but none can exist since you're duplicating all of the non-Identity columns. Looks like it's time for a cursor after all. – Damien_The_Unbeliever May 02 '13 at 14:49
Darn. Well thank you for your help, both trying to find an answer and in asking better questions. – Garrett Daniel DeMeyer May 02 '13 at 14:56
Since SQL 2005 was not specified, this should be marked as answer. – Jul 20 '15 at 15:01
You've got a 500 bounty on it's way @Damien_The_Unbeliever, this helped me out a lot. – Jul 20 '15 at 15:24

score 0 · Answer 2 · answered Jul 20 '15 at 19:25

INSERT statements loading data into tables with an IDENTITY column are guaranteed to generate the values in the same order as the ORDER BY clause in the SELECT.

If you want the IDENTITY values to be assigned in a sequential fashion that follows the ordering in the ORDER BY clause, create a table that contains a column with the IDENTITY property and then run an INSERT .. SELECT … ORDER BY query to populate this table.

From: The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

You can use this fact to match your old with your new identity values. First collect the list of primary keys that you intend to copy into a temporary table. You can also include your modified column values as well if needed:

select
    PrimaryKey,
    Col1
    --Col2... etc
into #NewRecords
from Table
--where whatever...

Then do your INSERT with the OUTPUT clause to capture your new ids into the table variable:

declare @TableVariable table (
    New_ID int not null
);

INSERT INTO #table
    (Col1 /*,Col2... ect.*/)
OUTPUT INSERTED.PrimaryKey INTO @NewIds
SELECT Col1 /*,Col2... ect.*/
from #NewRecords
order by PrimaryKey

Because of the ORDER BY PrimaryKey statement, you will be guaranteed that your New_ID numbers will be generated in the same order as the PrimaryKey field of the copied records. Now you can match them up by row numbers ordered by the ID values. The following query would give you the parings:

select PrimaryKey, New_ID
from
    (select PrimaryKey, 
        ROW_NUMBER() over (order by PrimaryKey) OldRow
    from #NewRecords
    ) PrimaryKeys
join
    (
    select New_ID, 
        ROW_NUMBER() over (order by New_ID) NewRow
    from @NewIds
    ) New_IDs
on OldRow = NewRow

Can I keep old keys linked to new keys when making a copy in SQL?

2 Answers2

Linked