Comparison Query to Compare Two SQL Server Tables

Question

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.

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.

Just trying to clarify your question. Which of the following are you interested in? 1. Column name differences between to tables where the column data is semantically the same? 2. Rows which are in one table but not in another? 3. Rows which are similar, but might differ on X number of columns? 4. Just which table has more records? — Scanningcrew, Sep 11 '09 at 15:56

score 20 · Answer 1 · edited Mar 21 '13 at 12:15

20

Select * from Table1
Except
Select * from Table2

It will show all mismatch records between table1 and table2

edited Mar 21 '13 at 12:15

Xavi López

27,550
11
97
161

answered Mar 20 '13 at 08:05

Fahad Sattar

201
2
2

Short and sweet (and effective). Exactly what I wanted. +1 – davidXYZ Apr 03 '13 at 21:23
7

Consider using `union` like this: `(Select * from Table1 Except Select * from Table2) UNION (Select * from Table2 Except Select * from Table1)`. This will give you any deleted or added rows in both of the tables. – slartidan Dec 03 '14 at 15:15
IMO slartidan's union comment is required for a complete answer. Without the union your result set will only contain values from table1 that are not in table2. Adding the union will give you records missing from both tables (as he stated). – ScottLenart May 26 '17 at 18:05

score 18 · Answer 2 · answered Oct 21 '15 at 17:15

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.

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/

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.

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.

score 6 · Accepted Answer · answered Sep 11 '09 at 17:45

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:

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 :)

Let me try your method. Thanks. – Tarik Sep 13 '09 at 00:55 — Tarik, Sep 13 '09 at 00:55

score 2 · Answer 4 · answered Aug 03 '10 at 15:44

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

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

score 2 · Answer 5 · answered Aug 04 '10 at 09:54

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

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 topic for more information.

score 0 · Answer 6 · edited Jul 30 '12 at 12:28

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

You should consider elaborating on your answer by providing some details about what it is you're doing and how it answers the OP's question. — forsvarir, Jul 30 '12 at 12:29

score 0 · Answer 7 · answered Apr 09 '10 at 11:28

0

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!

answered Apr 09 '10 at 11:28

Devart

119,203
23
166
186

score 0 · Answer 8 · answered Aug 19 '15 at 05:30

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)

score -1 · Answer 9 · answered Sep 12 '09 at 17:53

-1

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.

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

answered Sep 12 '09 at 17:53

Thanks, but actually I am not looking for software which can do what I want. I want to implement it by myself. Thanks. – Tarik Sep 13 '09 at 00:57
The link is dead by now. – Noe Nieto Mar 23 '15 at 19:43

Comparison Query to Compare Two SQL Server Tables

9 Answers9

Linked