Copy Master-Detail data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Copy Master-Detail data

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-07-27 : 07:16:08

I have tried to create an example to illustrate the problem I'm dealing with at the moment because I can't seem to explain it in words.

Please consider the following situation

declare @SomeTable table(ID int IDENTITY(1,1), someField varchar(50))
declare @TableMaster table(ID int IDENTITY(1,1), FK_Some int null, FK_1 int null, RepeatCount int null, aField varchar(50))
declare @TableDetail table(ID int IDENTITY(1,1), FK_Some int, FK_1 int, RepeatCount int null, FK_Master int, bField varchar(50))

insert into @SomeTable
values
('something')

insert into @TableMaster
values
(null, null, null,'MasterValue1'),
(1,1,null,'MasterValue2'),
(1,2,1,'MasterValue3'),
(1,2,2,'MasterValue4'),
(1,2,3,'MasterValue5'),
(1,1,null,'MasterValue6')

insert into @TableDetail
values
(1,1,null,1,'DetailValue1'),
(1,1,null,2,'DetailValue2'),
(1,2,1,1,'DetailValue3'),
(1,2,1,3,'DetailValue4'),
(1,2,2,1,'DetailValue5'),
(1,2,2,4,'DetailValue6'),
(1,2,3,1,'DetailValue7'),
(1,2,3,5,'DetailValue8'),
(1,1,null,6,'DetailValue9')

There is a fourth table, but since it is not relevant to the question I didn't include it. The foreign key FK_1 refers to this missing table.
The data should look like this

1	NULL	NULL	NULL	MasterValue1
2	1	1	NULL	MasterValue2
3	1	2	1	MasterValue3
4	1	2	2	MasterValue4
5	1	2	3	MasterValue5
6	1	1	NULL	MasterValue6


1	1	1	NULL	1	DetailValue1
2	1	1	NULL	2	DetailValue2
3	1	2	1	1	DetailValue3
4	1	2	1	3	DetailValue4
5	1	2	2	1	DetailValue5
6	1	2	2	4	DetailValue6
7	1	2	3	1	DetailValue7
8	1	2	3	5	DetailValue8
9	1	1	NULL	6	DetailValue9

At some point I'm inserting a new record to "SomeTable" (creating ID 2 in this example).
What I want to do is make a copy of all records in the MasterTable and the DetailTable where FK_Some is equal to 1, for FK_Some 2.
This should give the following output

1	NULL	NULL	NULL	MasterValue1
2	1	1	NULL	MasterValue2
3	1	2	1	MasterValue3
4	1	2	2	MasterValue4
5	1	2	3	MasterValue5
6	1	1	NULL	MasterValue6
7	2	1	NULL	MasterValue2
8	2	2	1	MasterValue3
9	2	2	2	MasterValue4
10	2	2	3	MasterValue5
11	2	1	NULL	MasterValue6


1	1	1	NULL	1	DetailValue1
2	1	1	NULL	2	DetailValue2
3	1	2	1	1	DetailValue3
4	1	2	1	3	DetailValue4
5	1	2	2	1	DetailValue5
6	1	2	2	4	DetailValue6
7	1	2	3	1	DetailValue7
8	1	2	3	5	DetailValue8
9	1	1	NULL	6	DetailValue9
10	2	1	NULL	1	DetailValue1
11	2	1	NULL	7	DetailValue2
12	2	2	1	1	DetailValue3
13	2	2	1	8	DetailValue4
14	2	2	2	1	DetailValue5
15	2	2	2	9	DetailValue6
16	2	2	3	1	DetailValue7
17	2	2	3	10	DetailValue8
18	2	1	NULL	11	DetailValue9

I've tried with the following, but since the key what makes a record unique in the Detail Table, includes the foreign key to the Master table, I keep getting duplicates.
Any suggestions on how to get my desired output?
Thanks in advance!

declare @tmpInserted table(aNewID int, FK_Some int null, FK_1 int null, RepeatCount int null, aField varchar(50))

insert into @TableMaster
output inserted.* into @tmpInserted
select 2, FK_1, RepeatCount, aField
from @TableMaster
where FK_Some = 1

insert into @TableDetail
select 2, det1.FK_1, det1.RepeatCount, det1.FK_Master, det1.bField
from @TableDetail as det1
where det1.FK_Some = 1
and det1.FK_Master not in (select mast1.ID from @TableMaster as mast1 where mast1.FK_Some = 1)
union all
select 2, det2.FK_1, det2.RepeatCount, tmp.aNewID, det2.bField
from @TableDetail as det2
inner join @tmpInserted as tmp
  on tmp.FK_Some = 2
  and tmp.FK_1 = det2.FK_1
  and (
        (tmp.RepeatCount = det2.RepeatCount)
        or
        (tmp.RepeatCount is null and det2.RepeatCount is null)
      )
where det2.FK_Some = 1
and det2.FK_Master in (select mast2.ID from @TableMaster as mast2 where mast2.FK_Some = 1)


select * from @TableMaster
select * from @TableDetail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 08:54:49

[code]
declare @SomeTable table(ID int IDENTITY(1,1), someField varchar(50))
declare @TableMaster table(ID int IDENTITY(1,1), FK_Some int null, FK_1 int null, RepeatCount int null, aField varchar(50))
declare @TableDetail table(ID int IDENTITY(1,1), FK_Some int, FK_1 int, RepeatCount int null, FK_Master int, bField varchar(50))

insert into @SomeTable
values
('something')

insert into @TableMaster
values
(null, null, null,'MasterValue1'),
(1,1,null,'MasterValue2'),
(1,2,1,'MasterValue3'),
(1,2,2,'MasterValue4'),
(1,2,3,'MasterValue5'),
(1,1,null,'MasterValue6')

insert into @TableDetail
values
(1,1,null,1,'DetailValue1'),
(1,1,null,2,'DetailValue2'),
(1,2,1,1,'DetailValue3'),
(1,2,1,3,'DetailValue4'),
(1,2,2,1,'DetailValue5'),
(1,2,2,4,'DetailValue6'),
(1,2,3,1,'DetailValue7'),
(1,2,3,5,'DetailValue8'),
(1,1,null,6,'DetailValue9')

declare @newid int = 2
declare @oldid int = 1

insert @TableMaster
select @newid, FK_1, RepeatCount, aField
from @TableMaster
where FK_Some = @oldid

insert @TableDetail
select @newid, FK_1, RepeatCount, FK_Master, bField
from @TableDetail
where FK_Some = @oldid
and FK_Master = 1

insert @TableDetail
select @newid, d.FK_1, d.RepeatCount, m2.id, d.bField
from @TableDetail d
join @TableMaster m1
on m1.FK_Some = @oldid
and m1.ID = d.FK_Master
and m1.FK_1 = d.FK_1
join @TableMaster m2
on m2.FK_Some = @newid
and m2.aField = m1.aField
where d.FK_Some = @oldid
and d.FK_Master <> 1

select * from @TableMaster
select * from @TableDetail
order by FK_Some, bField
[/code]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-07-27 : 09:11:35

Thank you nigel for the quick response!

Your query does indeed return the desired output.

I'm not sure if I will be able to join my Master and Detail tables on "aField" in my real live environment (this was just an example to demonstrate my problem) but let me see if I can adapt my query to your logic.
edit: just noticed you're joining the Master table with itself. That works for me.

Thanks again for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources