如何使用Sql Server 2008比较不同数据库中两个表之间的数据? [英] How to compare data between two table in different databases using Sql Server 2008?
问题描述
我在 Sql server 2008 中有两个数据库,分别命名为 DB1 和 DB2.这两个数据库具有相同的表和相同的表数据.但是,我想检查这些表中的数据之间是否存在差异.
I have two database's, named DB1 and DB2 in Sql server 2008. These two database's have the same tables and same table data also. However, I want to check if there are any differences between the data in these tables.
谁能帮我写一个脚本?
推荐答案
select *
from (
select 'T1' T, *
from DB1.dbo.Table
except
select 'T2' T, *
from DB2.dbo.Table
) as T
union all
select *
from (
select 'T2' T, *
from DB2.dbo.Table
except
select 'T1' T, *
from DB1.dbo.Table
) as T
ORDER BY 2,3,4, ..., 1 -- make T1 and T2 to be close in output 2,3,4 are UNIQUE KEY SEGMENTS
测试代码:
declare @T1 table (ID int)
declare @T2 table (ID int)
insert into @T1 values(1),(2)
insert into @T2 values(2),(3)
select *
from (
select *
from @T1
except
select *
from @T2
) as T
union all
select *
from (
select *
from @T2
except
select *
from @T1
) as T
结果:
ID
-----------
1
3
注意:在开发tuned"时,比较大表可能需要很长时间.解决方案或重构,这将给出与 REFERERCE 相同的结果 - 首先检查简单参数可能是明智的:像
Note: It can take long time to compare big table, when developing "tuned" solution or refactorig, which will give same result as REFERERCE - it may be wise to chekc simple parameters first: like
select count(t.*) from (
select count(*) c0, SUM(BINARY_CHECKSUM(*)%1000000) c1 FROM T_REF_TABLE
-- select 12345 c0, -214365454 c1 -- constant values FROM T_REF_TABLE
except
select count(*) , SUM(BINARY_CHECKSUM(*)%1000000) FROM T_WORK_COPY
) t
当此项为空时,您可能已经掌握了一些东西,并且您可能会在失败时进行修改,您将看到来自 T_REF 的常量值";为下一次检查节省更多时间!!!
When this is empty, you have probably things under controll, and may be you can modify when you fail you will see "constant values FROM T_REF" to isert to save even more time for next check!!!
这篇关于如何使用Sql Server 2008比较不同数据库中两个表之间的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!