How to copy tables avoiding cursors in SQL?

Question

I want to write a script in SQL that will copy these 2 tables (A, B) to the other 2 tables (C, D) with the same structure as A, B.

IMPORTANT :

• Tables C, D are NOT required empty
• Several processes can invoke a script at the same time.

Table A has the 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 | ------------------------ ----------------- 

Say that after copying table B to D, this is what I get

  ________________ | Table D | |______________| | id visible | | ----- -------| | 51 true | | 52 false | ---------------- 

Now, when I copy table A to C, I need to somehow find out that ID = 21 is now mapped to ID = 51, and ID = 32 to ID = 52. Finally, table C will look like this:

  ________________________ | Table C | |______________________| | id FK_C_D name | | ----- -------- ------| | 61 51 n1 | | 62 52 n2 | ------------------------ 

Since several processes can invoke a script at the same time, I CANNOT modify tables A, B to add some auxiliary columns. So, for this I used CURSOR. I copied row by row of table B and managed the temporary table to map OldId to NewId (21-> 51.32-> 52), and then used this temporary table to copy table A.

I read that CURSOR is bad practice. So, is there any other way to do this?

thanks

Answer 1

You can use the output clause with the merge operator to get a mapping between the source identifier and the target identifier. Described in this question. Using merge..output to get the mapping between source.id and target.id

Here is some code you can check. I use table variables instead of real tables.

Setting sample data:

 -- @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') 

Copy data:

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

Result:

 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 

Here you can test the code: http://data.stackexchange.com/stackoverflow/q/101643/using-merge-to-map-source-id-to-target-id

Answer 2

eg. SQL Server adds rowguid fields to tables that are included in some publications for merge replication. I think this approach can be used in your task. The idea is to add a pair of GUID fields that will act as global pf identifiers, so we can use them in both pairs of master-datails tables

Answer 3

You can do something like this:

 if object_id('tempdb..#TableB') is not null drop table #TableB select identity(int) RowId, * into #TableB from TableB if object_id('tempdb..#TableDIds') is not null drop table #TableDIds create table #TableDIds (RowId int identity(1,1), Id int) insert TableD output inserted.Id into #TableDIds select Visible from #TableB order by RowId insert TableC select tdi.Id, ta.name from TableA ta join #TableB tb on ta.FK_A_B = tb.Id join #TableDIds tdi on tdi.RowId = tb.RowId 

I used the following setting:

 create table TableB ( Id int not null primary key, Visible bit not null ) create table TableA ( Id int not null, FK_A_B int not null foreign key references TableB(Id), Name varchar(10) not null ) create table TableD ( Id int identity(1,1) primary key, Visible bit not null ) create table TableC ( Id int identity(1,1), FK_C_D int not null references TableD(Id), Name varchar(10) not null ) insert TableB values (21, 1), (32, 0) insert TableA values (1, 21, 'n1'), (5, 32, 'n2') 

Answer 4

I hope this helps

http://pastebin.com/embed_iframe.php?i=fkUd4RPY