Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
SQL Server doesn't have "ON DUPLICATE KEY UPDATE" –  gbn Sep 9 '11 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 Sep 9 '11 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? –  Nick Chammas Sep 12 '11 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 Sep 13 '11 at 1:47

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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