SQL Fastest Way to find Data Difference Between Two Tables

Question

I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that is new or has changed from the text files. The comparison is done on the primary key.

create table dbo.CustomerTransaction
(
    CustomerTransactionId int primary key,
    CustomerName varchar(50),
    ProductName varchar(50),
    QuantityBought int
)

So row values Table 1: (1,'Bob','Table',8) is the Same as Table 2: (1,'Bob','Table',8)

These are different (1,'Bob','Table',8) , (1,'Bob','Chair',8) , different on the primary key.

I am seeing a lot of articles discussing methods, with Left Join, Except, tablediff, Visual Studio DataComparison, Union group.

However, no one talks about performance/speed. Which is the fastest internal algorithm way? I am guessing it is the TableDiff Utility, which is a SQL tool designed specifically for this purpose.

We do not have access to CDC in legacy text file system, so we are extracting all new data into SQL Server daily, and comparing to previous data. We are finding incremental load values, and placing into Kimball Data Warehouse.

Neither table is actively being used at the time we are doing the comparison.

How to find out the content differences between 2 SQL tables and produce sync SQL https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables

Answer 1

There is no firm answer. David Lozinski conducted a study below, and found different methods succeed depending on row count, statistics, cardinality.

Fastest way to insert new records where one doesn’t already exist

I had an opportunity to test the tablediff utility, for some reason it was performing lot slower (2-3x slower) compared to T-SQL methods below. However it is good for conducting cross-server table differences (originally used in Replication method).