create table MasterTable (masterId int identity, masterType varchar(32))
create table DetailsTable (detailId int identity, detailMaster int, detailType varchar(32), detailValue varchar(32))
insert into MasterTable (masterType) values ('original')
insert into MasterTable (masterType) values ('original')
insert into MasterTable (masterType) values ('original')
insert into DetailsTable (detailMaster, detailType, detailValue)
select masterId, masterType, CHAR(ASCII('A')+masterId-1) from MasterTable
insert into DetailsTable (detailMaster, detailType, detailValue) values (3, 'original', 'D')
insert into DetailsTable (detailMaster, detailType, detailValue) values (null, 'random', 'R')
insert into MasterTable values ('masteronly')
insert into MasterTable values ('masteronly')
insert into MasterTable values ('masteronly')
select * from MasterTable
select * from DetailsTable
declare @temporaryValues table (
newMasterId int
,oldMasterId int
)
merge MasterTable as target
using (
select
detailMaster as oldMasterId
,row_number() over(partition by detailMaster order by detailMaster) as rownum
from DetailsTable
where detailType = 'original'
) as source (
oldMasterId
,rownum
)
on (0=1)
when not matched and rownum=1 then
insert (masterType)
values ('duplicate')
output
inserted.masterId
,source.oldMasterId
into @temporaryValues
;
insert into DetailsTable (detailMaster, detailType, detailValue)
select temps.newMasterId, 'duplicate', originals.detailValue
from DetailsTable originals
inner join @temporaryValues temps
on originals.detailMaster = temps.oldMasterId
and originals.detailType = 'original'
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)