在SQL中进行复制时，可以将旧键链接到新键吗? [英] Can I keep old keys linked to new keys when making a copy in SQL?

问题描述

我试图在SQL Server 2005中复制表中的记录并使用存储过程更改一些值.这很简单，但是我还需要使用新的主键复制其他表中的关系.由于此proc用于批量复制记录，因此我发现很难存储旧键和新键之间的某些关系. 现在，我正在使用OUTPUT INTO从批处理插入中获取新密钥. 例如:

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)?

我知道游标是一种选择，但是我从未使用过游标，只是听说过它们以恐怖故事的方式被引用.我更喜欢使用OUTPUT INTO或类似的东西.

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.

推荐答案

如果需要在临时表中同时跟踪旧键和新键，则需要作弊并使用MERGE:

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

数据设置:

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

并替换您的INSERT语句:

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;

产生:

ID  Col1
5   abc
12  def
19  abc
26  def

Old_ID  New_ID
5       19
12      26

之所以要这样做，是因为 OUTPUT子句与INSERT一起使用时-您只能访问inserted表，而不能访问任何可能属于SELECT的表.

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.

相关-MERGE滥用

这篇关于在SQL中进行复制时，可以将旧键链接到新键吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！