如何复制表以避免SQL中的游标? [英] How to copy tables avoiding cursors in SQL?
问题描述
我想用 SQL 编写脚本，将这两个表(A，B)复制到其他 2 个表(C，D)，结构与 A，B 相应.
I want to write script in SQL that will copy these 2 tables(A,B) to other 2 tables(C,D) with the same structure as A,B accordingly.
重要事项:
- 表 C、D 不需要必须为空
- 多个进程可能同时调用脚本
表A有表B的外键(fk_a_b)
Table A has foreign key(fk_a_b) of table B
________________________ _________________
| Table A | | Table B |
|______________________| |_______________|
| id FK_A_B name | | id visible |
| ----- -------- ------| | ----- --------|
| 1 21 n1 | | 21 true |
| 5 32 n2 | | 32 false |
------------------------ -----------------
假设将表 B 复制到 D 后，这就是我得到的
Let say that after copying table B to D this is what I get
________________
| Table D |
|______________|
| id visible |
| ----- -------|
| 51 true |
| 52 false |
----------------
现在，当我将表 A 复制到 C 时，我需要知道，ID=21 现在映射到 ID=51，ID=32 映射到 ID=52.最后，表 C 将是:
Now, when I'll copy table A to C I need to know, somehow, that ID=21 maps now to ID=51, and ID=32 to ID=52. Finally, the table C will be:
________________________
| Table C |
|______________________|
| id FK_C_D name |
| ----- -------- ------|
| 61 51 n1 |
| 62 52 n2 |
------------------------
由于多个进程可能同时调用脚本，我无法更改表 A、B 以添加一些辅助列.因此，为了实现这一点，我使用了 CURSOR.我逐行复制表 B 和管理临时表以将 OldId 映射到 NewId(21->51,32->52)，然后使用此临时表复制表 A.
Because several processes may call script simultaneously, I CAN'T alter table A,B to add some helper columns. So, to achieve this I used CURSOR. I copied row by row of table B and managed temp table to map OldId to NewId(21->51,32->52) and then used this temp table to copy table A.
我读过 CURSOR 是不好的做法.那么，还有其他方法吗?
I've read that CURSOR is bad practice. So, is there another way to do it?
谢谢
推荐答案
您可以在 merge 语句中使用 output 子句来获取源 id 和目标 id 之间的映射.在这个问题中描述.使用merge..output获取映射source.id 和 target.id 之间
You can use the output clause with the merge statement to get a mapping between source id and target id. Described in this question. Using merge..output to get mapping between source.id and target.id
这是一些您可以测试的代码.我使用表变量而不是真正的表.
Here is some code that you can test. I use table variables instead of real tables.
设置示例数据:
-- @A and @B is the source tables
declare @A as table
(
id int,
FK_A_B int,
name varchar(10)
)
declare @B as table
(
id int,
visible bit
)
-- Sample data in @A and @B
insert into @B values (21, 1),(32, 0)
insert into @A values (1, 21, 'n1'),(5, 32, 'n2')
-- @C and @D is the target tables with id as identity columns
declare @C as table
(
id int identity,
FK_C_D int not null,
name varchar(10)
)
declare @D as table
(
id int identity,
visible bit
)
-- Sample data already in @C and @D
insert into @D values (1),(0)
insert into @C values (1, 'x1'),(1, 'x2'),(2, 'x3')
复制数据:
-- The @IdMap is a table that holds the mapping between
-- the @B.id and @D.id (@D.id is an identity column)
declare @IdMap table(TargetID int, SourceID int)
-- Merge from @B to @D.
merge @D as D -- Target table
using @B as B -- Source table
on 0=1 -- 0=1 means that there are no matches for merge
when not matched then
insert (visible) values(visible) -- Insert to @D
output inserted.id, B.id into @IdMap; -- Capture the newly created inserted.id and
-- map that to the source (@B.id)
-- Add rows to @C from @A with a join to
-- @IdMap to get the new id for the FK relation
insert into @C(FK_C_D, name)
select I.TargetID, A.name
from @A as A
inner join @IdMap as I
on A.FK_A_B = I.SourceID
结果:
select *
from @D as D
inner join @C as C
on D.id = C.FK_C_D
id visible id FK_C_D name
----------- ------- ----------- ----------- ----------
1 1 1 1 x1
1 1 2 1 x2
2 0 3 2 x3
3 1 4 3 n1
4 0 5 4 n2
您可以在这里测试代码:https://data.stackexchange.com/stackoverflow/q/101643/using-merge-to-map-source-id-to-target-id
You can test the code here: https://data.stackexchange.com/stackoverflow/q/101643/using-merge-to-map-source-id-to-target-id
这篇关于如何复制表以避免SQL中的游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
