ON DUPLICATE KEY UPDATE question 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 &lt;&gt; 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;
If you're using SQL 2008, look at the MERGE statement, it can do what other platforms call UPSERT, UPDATE if it exists, otherwise INSERT.