CODEWITHSUNDEEP
sqlsql-server
Sast77
Sast77

Reputation: 97

SQL Query to compare fields between Table A and B for duplicate values, and use non-match records in Table B to append to Table A

I am using SQL SERVER. I have two database tables with 20 fields each, and I need to compare ALL fields in both tables to determine if Table2 has duplicates of records in Table1, and append the non-matching records from Table2 to Table1.

I saw several suggestions, but want the best option to compare both tables and append the non-matching records to Table1.

Please let me know if Option 1 SQL sample below is correct and can do the job, or whether I can use Option 2 also shown below. My problem with Option 2 is that I am not sure how to use it, and would appreciate some help/suggestions. Thanks a mil.

Option 1:

UPDATE Table1
SET [Num] = A.[Num]
      ,[Desc] = A.[Desc]
      ,[EmpName] = A.[EMPL_NAME]
      ,[EmpNum] = A.[EMPL_NUM]
WHERE(
    SELECT  *   
    FROM Table2 A  
    WHERE NOT EXISTS (SELECT *      
                      FROM Table1 B
                      WHERE A.[Num] = B.[Num] and
                            A.[Desc] = B.[Desc] and
                            A.[EmpName] = B.[EMPL_NAME] and
                            A.[EmpNum] = B.[EMPL_NUM] and
                      )
             )

Option 2:

REPLACE INTO Table1
SELECT * FROM Table2;

This is the current data example:

Table1
    Num Desc        EmpName EmpNum
    ------------------------------
    100 aaaaaaaaaaa bbb     null
    200 ccccccccccc ddd     eee
    300 fffffffffff null    kkk

Table2 
    Num Desc        EMPL_NAME EMPL_NUM
    ----------------------------------
    100 aaaaaaaaaaa ccc       ddd
    200 ccccccccccc ddd       eee
    300 ddddddddddd kkk       mmm
    400 nnnnnnnnnnn ppp       www

This is the expected result:- 

Table1 
    Num Desc        EmpName EmpNum
    ------------------------------
    100 aaaaaaaaaaa ccc     ddd
    200 ccccccccccc ddd     eee
    300 ddddddddddd kkk     mmm
    400 nnnnnnnnnnn ppp     www

Upvotes: 0

Views: 637

Answers (1)

Sean
Sean

Reputation: 573

The below operation will delete records from Table1 where the primary key exists in Table2, and then do a full insert of all records in T2 that don't currently exist in T1 by primary key. This will function the same way as updating the rows individually, with the benefit of being set-based and quicker. 

DELETE a
FROM   table1 a
WHERE  EXISTS (SELECT 1
               FROM   table2 b
               WHERE  a.num = b.num);

INSERT INTO table1
            (num,
             desc,
             empl_name,
             empl_num)
SELECT num,
       desc,
       empl_name,
       empl_num
FROM   table2 t2
WHERE  NOT EXISTS (SELECT 1
                   FROM   table1 t1
                   WHERE  t1.num = t2.num)

Please let me know if there any restrictions that would prevent this solution for your situation.

Upvotes: 1

Related Questions