Compare Two Tables With Identical Fields

60 pts.
Tags:
SQL
SQL tables
Need to compare two tables A & B with identical fields and report when rows don't match and rows from B are missing in A.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Something like this should do the trick:
<pre>
—* FIND THE ROWS IN TABLE TTESTA THAT HAVE NO MATCH IN TTESTB
—*
SELECT COL1, COL2
FROM TTESTA TA
WHERE NOT EXISTS (SELECT 1
FROM TTESTB TB
WHERE TA.COL1 = TB.COL1
AND TA.COL2 = TB.COL2)
;
</pre>

An outer join could also be used.

If you could match the data using only the Primary Key, it would run faster because it would only need to use the indices. Are you sure you need to match every column?

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jaggerdude
    There are only 4 fields with no primary key. How about if I just want to match the two tables and report which ones don't match and which ones are missing from either tables?
    60 pointsBadges:
    report
  • Kccrosser
    When trying to find data like this, I prefer to run three separate queries and use UNION ALL to combine the results, as in the following example. The first block finds records in t1 where there is no record with the same "key" (k1) in t2. The second block finds records where the same "key" exists in both tables, but the data column is different. The third block finds records in t2 that have no corresponding "key" in t1. Depending on what you meant by "identical fields", this could be simple (like this example), or complex, if you are trying to resolve combinations of fields without common primary keys.
    create table t1 (
    	k1	int not null,
    	d2	varchar(255));
    create table t2 (
    	k1	int not null,
    	d2	varchar(255));
    insert into t1 values (1,'1.1');
    insert into t1 values (2,'1.2');
    insert into t1 values (3,'1.3');
    insert into t2 values (1,'1.1');
    insert into t2 values (2,'2.2');
    insert into t2 values (4,'2.4');
    go
    
    select 'in t1, not in t2' as DiffType,
    	t1.k1, t1.d2
    from
    	t1 
    where not exists (select 1 from t2 where t2.k1 = t1.k1)
    union all
    select 'in t1, different in t2' as DiffType,
    	t1.k1, t1.d2
    from
    	t1 
    where exists (select 1 from t2 where t2.k1 = t1.k1 and t2.d2 <> t1.d2)
    union all
    select 'in t2, not in t1' as DiffType,
    	t2.k1, t2.d2
    from
    	t2 
    where not exists (select 1 from t1 where t2.k1 = t1.k1);
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following