问题描述
我有两个数据库(在同一服务器上),它们具有相同的sceme.恐怕一个或多个表的数据之间存在差异,我想检查一下.
如果有一种方法可以在一个查询中比较整个数据库(所有表数据),那将是最好的.
否则,一次一张桌子也可以.
我应该打电话给我尝试使用的电话:
Hi,
I have two databases (on the same server) which have the same sceme. I''m afraid there''s a difference between one or more table''s data, and I''d like to check it.
If there''s a way to compare the entire DB (all tables data) in one query - it would be the best.
otherwise, one table at a time will be good too.
I should tel you that I tries using:
select *
from (
select *
from DB1.dbo.Table
except
select *
from DB2.dbo.Table
) as T
union all
select *
from (
select *
from DB2.dbo.Table
except
select *
from DB1.dbo.Table
) as T
结构,但我得到:
消息421,第16级,状态1,第1行
无法将图像数据类型选择为DISTINCT,因为它不具有可比性.
消息402,级别16,状态1,第1行
数据类型ntext和ntext在等于运算符中不兼容.
谢谢
stracture, but I get:
Msg 421, Level 16, State 1, Line 1
The image data type cannot be selected as DISTINCT because it is not comparable.
Msg 402, Level 16, State 1, Line 1
The data types ntext and ntext are incompatible in the equal to operator.
Thanks
推荐答案
SELECT ID
FROM DB1.dbo.Table
WHERE ID NOT IN (SELECT ID
FROM DB2.dbo.Table)
更改比较:
如果两个表都包含具有最后更改日期的字段,则可以将它们与以下查询进行比较:
Changes comparision:
If both tables contains fields with date of last change, you can compare them with the following query:
SELECT ID
FROM DB1.dbo.Table
WHERE ID NOT IN (SELECT T1.ID
FROM DB1.dbo.Table AS T1 INNER JOIN DB2.dbo.Table AS T2 ON T1.ID = T2.ID
WHERE (T1.DateOfLastChange = T2.DateOfLastChange)
但是,如果您的表不包含这些字段,则比较几乎是不可能的:(
要比较这些表,您应该比较两个表中的每个记录和每个列.
But if your tables doesn''t contain these fields, comparision is almost impossible :(
To compare those tables, you should compare each record and each column in both tables.
这篇关于比较来自两个不同数据库的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!