Consolidate duplicate recordid references into a single reference

[bmacbmac]

Hello. In SQL 2008 I have the following data:

create table #sourcedocinfo (irecordid int, sdocumentno varchar(200), sinstrumenttype varchar(200), dtfileddate datetime)
insert into #sourcedocinfo values(1,'97055612', 'MORT', '12-01-1990')
insert into #sourcedocinfo values(2,'97055612', 'MORT', '12-01-1990')
insert into #sourcedocinfo values(3,'97055612', 'MORT', '12-01-1990')
insert into #sourcedocinfo values(4,'12345', 'ORD', '01-01-1991')
insert into #sourcedocinfo values(5,'12345', 'ORD', '01-01-1991')
insert into #sourcedocinfo values(6,'55555', 'DT', '03-01-1992')
insert into #sourcedocinfo values(7,'55555', 'DT', '03-01-1992')
insert into #sourcedocinfo values(8,'55555', 'DT', '03-01-1992')
insert into #sourcedocinfo values(9,'77777', 'EAS', '04-01-1993')
insert into #sourcedocinfo values(10,'77777', 'EAS', '04-01-1993')
insert into #sourcedocinfo values(11,'77777', 'EAS', '04-01-1993')
insert into #sourcedocinfo values(12,'77777', 'EAS', '04-01-1993')
insert into #sourcedocinfo values(13,'77777', 'EAS', '04-01-1993')


create table #sourceparty (ipartyid int, irecordid int, snamelast varchar(75), partytype int)
insert into #sourceparty values (20, 1, 'SMITH', 1)
insert into #sourceparty values (21, 2, 'JONES', 1) 
insert into #sourceparty values (22, 3, 'JOHNESON', 2) 
insert into #sourceparty values (23, 4, 'KRINKS', 1) 
insert into #sourceparty values (24, 5, 'POWELL', 2) 
insert into #sourceparty values (25, 6, 'KIMMEL', 1) 
insert into #sourceparty values (26, 7, 'KIMBALL', 2) 
insert into #sourceparty values (27, 8, 'FINKLE', 2) 
insert into #sourceparty values (28, 9, 'GEORGE', 1) 
insert into #sourceparty values (29, 10, 'HUNTSMAN', 1) 
insert into #sourceparty values (30, 11, 'WASATCH', 1) 
insert into #sourceparty values (31, 12, 'BROWN', 1) 
insert into #sourceparty values (32, 13, 'ENDERS', 1)


create table #sourcerelated (irelatedid int, irecordid int, srdocumentno varchar(200))
insert into #sourcerelated (1, 1, '111111')
insert into #sourcerelated (2, 6, '321321')
insert into #sourcerelated (3, 9, '114114')
insert into #sourcerelated (4, 10, 556556')

A "document" will be distinct sdocumentno, sinstrumenttype, dtfileddate. So, #sourcedocinfo has 4 unique documents.

I would like to consolidate these records into one irecordid, which I can do with the following:

create table #finaldocinfo (irecordid int, sdocumentno varchar(200), sinstrumenttype varchar(200), dtfileddate datetime)
insert into #finaldocinfo
select MIN(irecordid), sdocumentno, sinstrumenttype, dtfileddate
from #sourcedocinfo
group by sdocumentno, sinstrumenttype, dtfileddate
order by MIN(irecordid)

My question relates to #sourceparty and #sourcerelated. I would like to set their irecordid's to match the single[/] recordid created for each document.. like this:

create table #finalparty (ipartyid int, irecordid int, snamelast varchar(75), partytype int)
insert into #finalparty values (20, 1, 'SMITH', 1)
insert into #finalparty values (21, 1, 'JONES', 1) 
insert into #finalparty values (22, 1, 'JOHNESON', 2) 
insert into #finalparty values (23, 4, 'KRINKS', 1) 
insert into #finalparty values (24, 4, 'POWELL', 2) 
insert into #finalparty values (25, 6, 'KIMMEL', 1) 
insert into #finalparty values (26, 6, 'KIMBALL', 2) 
insert into #finalparty values (27, 6, 'FINKLE', 2) 
insert into #finalparty values (28, 9, 'GEORGE', 1) 
insert into #finalparty values (29, 9, 'HUNTSMAN', 1) 
insert into #finalparty values (30, 9, 'WASATCH', 1) 
insert into #finalparty values (31, 9, 'BROWN', 1) 
insert into #finalparty values (32, 9, 'ENDERS', 1)

create table #finalrelated (irelatedid int, irecordid int, srdocumentno varchar(200))
insert into #finalrelated (1, 1, '111111')
insert into #sourcerelated (2, 6, '321321')
insert into #finalrelated (3, 9, '114114')
insert into #finalrelated (4, 9, 556556')

Can anyone think of a way I can do this? I have to import this data into another database where the sdocumentno, sinstrumentytype, and dtfileddate data must be unique, but doing so will break the irecordid join between the two tables.

Thanks!

 

[bmacbmac]

I am considering adding sdocumentno, sinstrumenttype, dtfileddate to #sourceparty and #sourcerelated and then doing the following:

update #sourceparty
set sdocumentno = #sourcedocinfo.sdocumentno
from #sourceparty
join #sourcedocinfo on #sourcedocinfo.irecordid = #sourceparty.irecordid

update #sourceparty
set sinstrumenttype = #sourcedocinfo.sinstrumenttype
from #sourceparty
join #sourcedocinfo on #sourcedocinfo.irecordid = #sourceparty.irecordid

update #sourceparty
set dtfileddate = #sourcedocinfo.dtfileddate
from #sourceparty
join #sourcedocinfo on #sourcedocinfo.irecordid = #sourceparty.irecordid

Then...
create my unique irecordid's in #finaldocinfo



Then...

update #sourceparty
set irecordid = #finaldocinfo.irecordid
from #sourceparty
join #finaldocinfo on #finaldocinfo.sdocumentno = #sourceparty.sdocumentno
and #finaldocinfo.sinstrumenttype = #sourceparty.sinstrumenttype
and #finaldocinfo.fileddate = #sourceparty.dtfileddate

But I'm sure there is a better way.