I'm trying to compare data from two systems. The basic idea is:
- Create Temp Table.
- Insert Table1 Data.
- Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected.
- Filter where first or second table has different info.
I'm trying to get the second set of inserts, from the second system, to insert, and if duplicate, update instead. I can't seem to get it right. For the final insert, what should I have?
I don't have the "Filter Part" because I don't have the Table2 data inserted. The filter part would show rows where Active, Last or First name is different between the two systems. Simple
where Table1LastName <> Table2LastName type statements.
Note: I have this working with 2 temp tables created... and a full outer join and some "Where ID is null". I'm just trying to get this to work in a manner I haven't used before.
if object_id('tempdb..#CompareDrs') is not null drop table #CompareDrs; GO create table #CompareDrs ( ID varchar(20) unique ,TABLE1Active varchar(1) ,TABLE1LastName varchar(50) ,TABLE1FirstName varchar(50) ,TABLE2Active varchar(1) ,TABLE2LastName varchar(50) ,TABLE2FirstName varchar(50) ); GO INSERT INTO #CompareDrs (ID, TABLE1Active, TABLE1LastName, TABLE1FirstName) SELECT right('00000000000000000000' + ForeignID, 20) ID ,case when Active = 0 then 'I' when Active = 1 then 'A' else ' ' end TABLE1Active ,LastName TABLE1LastName ,FirstName TABLE1FirstName FROM [DATABASE1].dbo.DRs Order By ID; GO INSERT Into #CompareDrs (ID, TABLE2Active, TABLE2LastName, TABLE2FirstName) SELECT right('00000000000000000000' + Cast(DRDR# as Varchar(50)), 20) ID ,DRAFLG TABLE2Active ,DRLNAM TABLE2LastName ,DR1NAM TABLE2FirstName FROM [LINKEDERVER].[DATABASE].[CATALOG].[TABLE] DR ON DUPLICATE KEY UPDATE TABLE2Active = VALUES(TABLE2Active), TABLE2LastName = VALUES(TABLE2LastName), TABLE2FirstName = VALUES(TABLE2FirstName), GO SELECT * FROM #CompareDrs;