如何扫描两个查询之间的差异

Question

我有一个每天加载新数据的表和另一个包含该表更改历史记录的表。 检查自上次加载数据以来是否有任何数据发生更改的最佳方法是什么？

例如，我有表格@a，其中包含针对不同国家/地区的一些策略，表格@b跟踪对表格@a所做的更改。 我可以使用 checksum（） 对可以更改的字段进行哈希处理，如果现有哈希与新哈希不同，则将它们添加到表中。 但是，MSDN 认为这不是一个好主意，因为可能会发生"冲突"，例如，两个不同的值映射到同一个校验和。

用于校验和的 MSDN 链接http://msdn.microsoft.com/en-us/library/aa258245（v=SQL.80）.aspx

示例代码：

declare @a table
(
    ownerid bigint
    ,Strategy varchar(50)
    ,country char(3)
)
insert into @a
select 1,'Long','USA'
insert into @a
select 2,'Short','CAN'
insert into @a
select 3,'Neutral','AUS'
declare @b table
(
    Lastupdated datetime
    ,ownerid bigint
    ,Strategy varchar(50)
    ,country char(3)
)
insert into @b
(
    Lastupdated
    ,ownerid
    ,strategy
    ,country
)
select 
    getdate()
    ,a.ownerid
    ,a.strategy
    ,a.country
from @a a left join @b b
    on a.ownerid=b.ownerid
where
    b.ownerid is null
select * from @b
--get a different timestamp
waitfor delay '00:00:00.1'
--change source data
update @a 
set strategy='Short'
where ownerid=1
--add newly changed data into 
insert into @b
select 
    getdate()
    ,a.ownerid
    ,a.strategy
    ,a.country
from 
    (select *,checksum(strategy,country) as hashval from @a) a 
    left join 
    (select *,checksum(strategy,country) as hashval from @b) b
    on a.ownerid=b.ownerid
where 
    a.hashval<>b.hashval
select * from @b

Answer 1

使用 EXCEPT 编写查询怎么样？只需为两个表编写查询，然后在它们之间添加EXCEPT：

(SELECT * FROM table_new) EXCEPT (SELECT * FROM table_old) 

结果将是table_new中不在table_old中的条目（即已更新或插入的条目）。

注意：要获取最近从table_old中删除的行，您可以颠倒查询的顺序。

Answer 2

如果您使用不同的方法来解决问题，则无需检查更改。

在主表上，为 INSERT 、 UPDATE 和 DELETE 创建一个触发器，该触发器通过写入表 @b 来跟踪更改。

如果您在互联网上搜索"SQL审计表"，您会发现许多描述该过程的页面，例如： 向 SQL Server 数据库添加简单的基于触发器的审核

Answer 3

多亏了@newenglander我才能使用 EXCEPT 来查找更改的行。 正如@Tony所说，我不确定多个更改将如何工作，但这是重新设计的相同示例代码以使用 Except 而不是 CHECKSUM

declare @a table
(
    ownerid bigint
    ,Strategy varchar(50)
    ,country char(3)
)
insert into @a
select 1,'Long','USA'
insert into @a
select 2,'Short','CAN'
insert into @a
select 3,'Neutral','AUS'
declare @b table
(
    Lastupdated datetime
    ,ownerid bigint
    ,Strategy varchar(50)
    ,country char(3)
)
insert into @b
(
    Lastupdated
    ,ownerid
    ,strategy
    ,country
)
select 
    getdate()
    ,a.ownerid
    ,a.strategy
    ,a.country
from @a a left join @b b
    on a.ownerid=b.ownerid
where
    b.ownerid is null
select * from @b
--get a different timestamp
waitfor delay '00:00:00.1'
--change source data
update @a 
set strategy='Short'
where ownerid=1

--add newly changed data using EXCEPT
insert into @b 
select getdate(),
    ownerid,
    strategy,
    country
from 
(
    (
    select 
        ownerid
        ,strategy
        ,country 
    from @a changedtable
    ) 
    EXCEPT 
    (
    select 
        ownerid
        ,strategy
        ,country 
    from @b historicaltable
    )
) x
select * from @b