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;

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)

INSERT INTO #CompareDrs (ID, TABLE1Active, TABLE1LastName, TABLE1FirstName)
         right('00000000000000000000' + ForeignID, 20) ID
        ,case when Active = 0 then 'I'
              when Active = 1 then 'A'
              else ' '
         end       TABLE1Active
        ,LastName  TABLE1LastName
        ,FirstName TABLE1FirstName
    Order By

INSERT Into #CompareDrs (ID, TABLE2Active, TABLE2LastName, TABLE2FirstName)
         right('00000000000000000000' + Cast(DRDR# as Varchar(50)), 20) ID
        ,DRAFLG TABLE2Active
        ,DRLNAM TABLE2LastName
        ,DR1NAM TABLE2FirstName
                        TABLE2LastName = VALUES(TABLE2LastName), 
                        TABLE2FirstName = VALUES(TABLE2FirstName),

SELECT * FROM #CompareDrs;
  • SQL Server doesn't have "ON DUPLICATE KEY UPDATE"
    – gbn
    Commented Sep 9, 2011 at 17:24
  • What would a similar option be then... to insert with unique keys respected? Removal of the ON DUPLICATE KEYS generates "Can't insert due to duplicate key" error.
    – WernerCD
    Commented Sep 9, 2011 at 18:38
  • Could you reword your question? It is unclear. Are you trying to merge the contents of two tables? Or are you interested in finding the differences between two tables? Commented Sep 12, 2011 at 23:52
  • Basically... "insert" into temp table with a unique field ID from a "local" table. "insert" into the SAME temp table from a "remote" table (With goal of comparing data) - Can't insert with unique ID's... how can I update OR insert in a single command?
    – WernerCD
    Commented Sep 13, 2011 at 1:47

3 Answers 3


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.


You could write a SELECT statement that captures the id's that have been inserted from the first insert into the temp table.

Then Simply INSERT from the 2nd table unless id's containted in your previous SELET statement. Use an ELSE statement to implement the other rows as an UPDATE


It looks like you're putting matching rows from two tables into a single row in another table.

If that's the case, then you need to

  • INSERT columns from the first table, and
  • UPDATE other columns from the second table where the ID numbers match and where the other columns are NULL.

I could be wrong about that, because it's not completely clear to me that the ID numbers on the two tables are going to match.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.