Sql-server – ON DUPLICATE KEY UPDATE question

insertsql servertemporary-tablesupsert

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;

Best Answer

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.

Related Question