我想用 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.
重要事项:
表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您可以在这里测试代码:data.stackexchange/stackoverflow/q/101643/using-merge-to-map-source-id-to-target-id
You can test the code here: data.stackexchange/stackoverflow/q/101643/using-merge-to-map-source-id-to-target-id
更多推荐
如何复制表以避免SQL中的游标?
发布评论