Compare Two SQL Tables for Unique Cells and Update Master Table

Question

I'm using SQL Server 2017 and I've been trying to figure this out for hours. My goal is to compare 2 tables and only insert NEW rows based on UNIQUE cells. All the columns have an ID number, but I have not assigned a primary key. My goal is to ONLY add extra rows containing UNIQUE cells if none of the criteria match. This is how my tables are setup now.

Old-Data (Table name is Test1)

FName  LNname   Address    City   State  Zipcode   Phone          Phone2    ID
Frank  Smith    444 Main  Y'all   TX     77484     281-788-9898   NULL      1
Thomas Parker   343 Tire  Y'all   TX     77484     281-788-5453   NULL      2
Ben    Krull    232 Wheel Y'all   TX     77484     281-788-9535   NULL      3

New-Data (Table name is Test2)

FName  LNname   Address    City State Zipcode  Phone          Phone2       ID
Frank  Smith    444 Main  Y'all TX    77484    281-788-9898   NULL         1
Thomas Parker   343 Tire  Y'all TX    77484    281-788-5453   NULL         2
Ben    Krull    232 Wheel Y'all TX    77484    281-788-9535   NULL         3
Juan   Roberto  444 Gas   Y'all TX    77484    281-788-3434   NULL         4
Ben    Krull    232 Wheel Y'all TX    77484    281-788-9535   713-545-4353 5

As you can see, ID's 1,2 and 3 are identical in both tables. ID-4 is a completely unique row, as is ID-5 because of the Phone2 entry. I found some code and modified it a bit to match the headers I care about it to help me determine what entries are duplicates or not. This is the code that has been driving me crazy.

INSERT TEST1 (Name 
    ,Last_Name 
    ,Address 
    ,City 
    ,State 
    ,Zip_Code 
    ,Phone 
    ,Phone2 
)
SELECT Name
    ,Last_Name 
    ,Address 
    ,City 
    ,State 
    ,Zip_Code 
    ,Phone 
    ,Phone2 

FROM TEST2
WHERE TEST2.NAME not in (select Name from test1)
AND   TEST2.Address not in (select Address from test1)
AND   TEST2.City not in (select City from test1)
AND   TEST2.State not in (select State from test1)
AND   TEST2.Zip_Code not in (select Zip_Code from test1)
AND   TEST2.Phone not in (select Phone from test1)
AND   TEST2.Phone2 not in (select phone2 from test1)

I'm trying to match all the fields and if a unique CELL is found the new row is entered into the old_data table. I see no errors after executing it, but nothing happens too. Interestingly enough, If I remove all the code below the line that says, "WHERE TEST2.NAME not in (select Name from test1)" ID-4 (Juan Roberto) is transferred over, but nothing happens with ID-5.

I'm really starting to think WHERE cannot be used to compare the duplicates and modify or add entries, but I could be wrong. A merge feature would be awesome, but I'm happy with just the former since I could always run a different script to clean up the table for dupes. I'm hoping somebody might be able to point me in the right direction since I've got millions of rows in different tables that need to be compared and trimmed down. Thanks.

Please read through this question: https://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice — Tim Biegeleisen, Jan 22 '18 at 06:22
Re "I found some code" - IMO this is not the way developers should work. — Arvo, Jan 22 '18 at 08:23

score 1 · Answer 1 · answered Jan 22 '18 at 07:30

Just try the following code, I am not sure about it will work for you, because I am not tested it

SELECT * INTO #TEMP FROM Test2(NOLOCK);

DELETE #TEMP
FROM   #TEMP
       INNER JOIN Test1
           ON #TEMP.NAME = Test1.NAME
              AND #TEMP.Address = Test1.Address
              AND #TEMP.City = Test1.City
              AND #TEMP.State = Test1.State
              AND #TEMP.Zip_Code = Test1.Zip_Code
              AND #TEMP.Phone = Test1.Phone
              AND #TEMP.Phone2 = Test1.Phone2 ;

INSERT INTO Test1
SELECT * FROM #TEMP;

Compare Two SQL Tables for Unique Cells and Update Master Table

1 Answers1