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
LEFT JOIN
vsNOT EXISTS
, and then here is one comparingNOT EXISTS
toNOT IN
.EXCEPT
looks at all columns, and since you are basing it off the PK only you wouldn't want to use it. – scsimon Oct 15 '18 at 18:18