本文介绍了比较来自两个不同数据库的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我有两个数据库(在同一服务器上),它们具有相同的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.


这篇关于比较来自两个不同数据库的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 13:59