比较来自两个不同数据库的数据 [英] Comparing data from two different databases

问题描述

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

推荐答案

阅读以下类似问题的答案:
如何比较两个表 [ http://blog.sqlauthority.com/2006/11/30/sql-server-cursor-to-process-tables-in-database-with-static-prefix-and-date-created/ [ ^ ]

Read the following answer to a similar question :
How to compare two table[^]

You can compare multiple tables with a cursor :
http://blog.sqlauthority.com/2006/11/30/sql-server-cursor-to-process-tables-in-database-with-static-prefix-and-date-created/[^]

不存在实现此目的的简单方法，尤其是当您尝试比较两个表中的所有字段时.

增长比较:
如果两个表都包含具有唯一值(主键)的字段，则可以将它们与以下查询进行比较:

A simple way to achieve this not exists, especially when you are trying to compare all fields in both tables.

Growth comparision:
If both tables contain fields with unique values (Primary Key), you can compare them with the following query:

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.

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