比较两个 SQL Server 表的比较查询

Comparison Query to Compare Two SQL Server Tables

提问by Tarik

I would like to know how to compare two different database table records. What I mean is I will compare two database tables which may have different column names but same data. But one of them may have more records than the other one so I want to see what the difference is between those two tables. To do that how to write the sql query ? FYI : these two databases are under the same SQL Server instance.

我想知道如何比较两个不同的数据库表记录。我的意思是我将比较两个可能具有不同列名但数据相同的数据库表。但是其中一个的记录可能比另一个多，所以我想看看这两个表之间有什么区别。要做到这一点，如何编写 sql 查询？仅供参考：这两个数据库位于同一个 SQL Server 实例下。

Table1
------+---------
|name |lastname|
------+---------
|John |rose    |
------+---------
|Demy |Sanches |
------+---------

Table2
------+----------
|name2|lastname2|
------+----------
|John |rose     |
------+----------
|Demy |Sanches  |
------+----------
|Ruby |Core     |
------+----------

Then when after comparing table 1 and table 2, it should return Ruby Core from Table2.

然后当比较表 1 和表 2 后，它应该从表 2 返回Ruby Core。

回答by Fahad Sattar

Select * from Table1
Except
Select * from Table2

It will show all mismatch records between table1and table2

它将显示table1和之间的所有不匹配记录table2

回答by Derrick Whitacker

Late answer but can be useful to other readers of this thread

迟到的答案，但可能对本主题的其他读者有用

Beside other solutions, I can recommend SQL comparison tool called ApexSQL Data Diff.

除了其他解决方案，我可以推荐名为 ApexSQL Data Diff 的 SQL 比较工具。

I know you'd prefer the solution not based on the software, but for other visitors, who may want to do this in an easier way, I strongly suggest reading this article: http://solutioncenter.apexsql.com/how-to-compare-sql-server-database-tables-with-different-names/

我知道您更喜欢不基于软件的解决方案，但对于可能希望以更简单的方式执行此操作的其他访问者，我强烈建议您阅读这篇文章：http: //solutioncenter.apexsql.com/how-to -compare-sql-server-database-tables-with-different-names/

The article explains how to use the Object mapping feature in ApexSQL Data Diff, which is particularly useful in situations where two tables share the same name, but their column names are different.

文章解释了如何使用ApexSQL Data Diff中的对象映射功能，这在两个表共享相同名称但它们的列名称不同的情况下特别有用。

To handle such a case - each column pair needs to be mapped manually in order for the data stored within them to be included when comparing SQL database tables for differences.

为了处理这种情况 - 需要手动映射每个列对，以便在比较 SQL 数据库表的差异时包含存储在其中的数据。

回答by Murph

If you do an outer join from T1 to T2 you can find rows in the former that are not in the latter by looking for nulls in the T2 values, similarly an outer join of T2 to T1 will give you rows in T2. Union the two together and you get the lot... something like:

如果您执行从 T1 到 T2 的外连接，您可以通过在 T2 值中查找空值来找到前者中不包含在后者中的行，类似地，T2 到 T1 的外连接将为您提供 T2 中的行。将两者结合在一起，你会得到很多……比如：

SELECT 'Table1' AS TableName, name, lastname FROM
    Table1 OUTER JOIN Table2 ON Table1.name = Table2.name2 
                             AND Table1.lastname = Table2.lastname
WHERE Table2.name2 IS NULL
UNION
SELECT 'Table2' AS TableName, name2 as name, lastname2 as lastname FROM
    Table2 OUTER JOIN Table1 ON Table2.name2 = Table1.name 
                             AND Table2.lastname2 = Table1.lastname
WHERE Table1.name IS NULL

That's off the top of my head - and I'm a bit rusty :)

那是我的头顶 - 我有点生疏:)

回答by Joe

If you are using Sql server use a full join. it does exactly the same as Murph said but in one command.

如果您使用的是 Sql 服务器，请使用完整连接。它与 Murph 所说的完全相同，但在一个命令中。

    SELECT 'Table1' AS TableName, name, lastname 
    FROM Table1 
FULL JOIN Table2 ON Table1.name = Table2.name2 
                                 AND Table1.lastname = Table2.lastname

回答by Rabid

You could use the CHECKSUMfunction if you're confident that the data is expressed identically.

CHECKSUM如果您确信数据的表达方式相同，则可以使用该函数。

Example:

例子：

if not OBJECT_ID('Table1', 'Table') is null drop table Table1
if not OBJECT_ID('Table2', 'Table') is null drop table Table2
create table table1
( id int identity(0, 1), 
   name varchar(128), 
   lastname varchar(128)
)
create table table2
( id int identity(0, 1), 
   name varchar(128), 
   lastname varchar(128)
)
insert into table1 (name, lastname) values ('John', 'rose')
insert into table1 (name, lastname) values ('Demy', 'Sanches')
insert into table2 (name, lastname) values ('John', 'rose')
insert into table2 (name, lastname) values ('Demy', 'Sanches')
insert into table2 (name, lastname) values ('Ruby', 'Core')

select 
    table2.*
from table1
     right outer join table2 on CHECKSUM(table1.name, table1.lastname) = CHECKSUM(table2.name, table2.lastname)
where table1.id is null

See the CHECKSUM MSDN topicfor more information.

有关详细信息，请参阅CHECKSUM MSDN 主题。

回答by Sreedharan

create table #test 
(
    Sno INT IDENTITY(1,1),
    ExpDate VARCHAR(50),
    Amt INT,
    Amt1 INT,
    Amt2 INT,
    SumoAmt INT
)

create table #test1 
(
    Sno INT IDENTITY(1,1),
    ExpDate VARCHAR(50),
    Amt INT,
    Amt1 INT,
    Amt2 INT,
    SumoAmt INT
)

INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,10,40)
INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,20,50)
INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,30,60)
INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',NULL,20,40,70)

INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,10,40)
INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,20,50)
INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,30,60)
INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',NULL,20,40,70)

SELECT MIN(TableName) as TableName, Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
FROM
(
  SELECT '#test' as TableName,Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
  FROM #test
  UNION ALL
  SELECT '#test1' as TableName,Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
  FROM #test1
) tmp
GROUP BY Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
HAVING COUNT(*) = 1
ORDER BY sno

回答by Sakthi Kumaran Suriya

If you want the differences from both the table.

如果你想要两个表的差异。

(SELECT     *, 'in Table1' AS Comments
FROM       Table1
EXCEPT
SELECT     * , 'in Table1' AS Comments
FROM        Table2)
UNION
(SELECT     *, 'in Table2' AS Comments
FROM       Table2
EXCEPT
SELECT     *, 'in Table2' AS Comments
FROM        Table1)

回答by Devart

Try dbForge Data Compare for SQL Server. It can compare and synchronize any database data. Quick, easy, always delivering a correct result. See how it flies on your database!

尝试SQL Server 的 dbForge 数据比较。它可以比较和同步任何数据库数据。快速、简单、始终提供正确的结果。看看它如何在您的数据库上运行！

回答by Devart

Firefly will do exactly what you're looking for. It lets you build two sql statements then compare the results of the sql queries showing missing rows and data differences. Each query can even come from a different database like oracle / sql server.

Firefly 将完全满足您的需求。它允许您构建两个 sql 语句，然后比较显示缺失行和数据差异的 sql 查询的结果。每个查询甚至可以来自不同的数据库，如 oracle/sql server。

http://download.cnet.com/Firefly-Data-Compare-Tool/3000-10254_4-10633690.html?tag=mncol

http://download.cnet.com/Firefly-Data-Compare-Tool/3000-10254_4-10633690.html?tag=mncol