SQL查找两个表之间的数据差异的最快方法 [英] SQL Fastest Way to find Data Difference Between Two Tables
问题描述
我正在尝试找到最快的方法来查找两个表之间的差异.第一个表每天都由文本文件填充，第二个表在我们的数据库中维护.我们需要添加文本文件中的新数据或已更改的数据.
比较是在主键上完成的.
创建表dbo.CustomerTransaction
(
CustomerTransactionId int主键，
CustomerName varchar(50)，
产品名称varchar(50)，
购买数量int
)
因此表1(1，'Bob'，'Table'，8)的行值与表2:(1，'Bob'，'Table'，8)的行值相同
这些是不同的(1，'Bob'，'Table'，8)， (1，'Bob'，'Chair'，8)，在主键上有所不同.
我看到很多文章讨论方法，包括Left Join，Except，tablediff，Visual Studio DataComparison，Union组.
但是，没有人谈论性能/速度.哪种内部算法最快?我猜它是TableDiff实用程序，它是专门为此目的设计的SQL工具.
我们无权访问旧文本文件系统中的CDC，因此我们每天将所有新数据提取到SQL Server中，并与以前的数据进行比较.我们正在寻找增量负载值，并将其放入Kimball数据仓库.
在进行比较时，两个表都没有被积极使用.
https://dba.stackexchange.com/questions/64878/how-to-find-out-the-content-differences-between-2-sql-tables-and-produce-sync-sq
https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables
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.
https://dba.stackexchange.com/questions/64878/how-to-find-out-the-content-differences-between-2-sql-tables-and-produce-sync-sq
https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables
推荐答案
这篇关于SQL查找两个表之间的数据差异的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
