• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

SQL to compare 2 records for differences

Shruks, my SQL must be very rusted.

I have 2 similar tables say with 3 columns (Code, columnA, columnB). Code is Primary Key and both table contains 10 records each. They are suppose to contain the same values for each record but are not.

How will a SQL be able to extract out the records that have differences in values. Below is the SQL I have in mind. But if i many columns, this is going to be very tedious. I am there are better alternative SQL than my below statement.

Select a.Code
From Table1 a, Table b
Where a.Code = b.Code
And (a.ColumnA <> b.ColumnB Or a.ColumnB <> b.ColumnB)
0
NicksonKoh
Asked:
NicksonKoh
3 Solutions
 
OP_ZaharinCommented:
- or you can use the following:

SELECT a.Code
FROM Table1 a
JOIN Table b ON a.Code = b.Code
WHERE a.ColumnA <> b.ColumnA
OR a.ColumnB <> b.ColumnB
0
 
CluskittCommented:
You can do that, but if you use JOIN it will be much faster:
SELECT a.Code
FROM Table1 a INNER JOIN Table b
ON a.Code = b.Code
WHERE (a.ColumnA <> b.ColumnA OR a.ColumnB <> b.ColumnB)

Alternately, you can try:
SELECT Code
FROM (SELECT * FROM TableA
    UNION ALL SELECT * FROM TableB) t
GROUP BY Code, ColumnA, ColumnB
HAVING COUNT(*)=1

You'll have to specify all columns, but at least it's just once.
0
 
NicksonKohAuthor Commented:
Thx. Problem is I have to do this for over 50+ tables in 10 database! Some tables have over 100 fields.

Cracking my head how to do this faster and I am also not sure if null fields will be an issue.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
CluskittCommented:
For null fields, you can simply use: ISNULL(ColumnA,'0'). I use '0' as a general case because it will be the same type as almost all field types. If you were to use '' for all, then numeric fields would throw an error.

As for not doing it by hand, you could get all the column names from the sys tables and then send it to a dynamic sql query. I've had something similar done to me once (though not from the sys tables), but I'm not sure I can adapt to your case. It used the STUFF function to feed a variable which would then be concatenated with the regular query. Probably someone can assist you in that. I'll try to get something working, but your best bet would be another expert.
0
 
Geert GOracle dbaCommented:
if both tables have same structure
why not use a minus ?

select * from tableA
minus
select * from tableB

for lots and lots and lots of database >>> use sql to generate your scripts

setup table with
dbname: name of databas
user, pass: user and password
tableA and tableB

with sqlplus:
set linesize 9999
set wrap on
set trimspool on
set serveroutput off
set echo off
set pagesize 0
spool script.sql
select 'select * from '||tableA||' minus select * from '||tableB||';' from setup;
spool off

off course, you'll have to script the login too, but i think you get the idea
0
 
lwadwellCommented:
Sometimes tedious is the best way ... you can write scripts, use spreadsheets etc to automate or semi-automate the much of the work.
One way to do a compare without having to specify every column, just the join ones; and in SQL Server, to use CHECKSUM() maybe.  You cannot join on it ... you still need to specify the PK join columns ... but if the values are the same in both rows, the checksum is too.
Example below -- note, I used a FULL OUTER JOIN in case rows existing in either table but not the other:
IF OBJECT_ID('tempdb..#tableA')IS NOT NULL DROP TABLE #tableA ; 
CREATE TABLE #tableA(
	id INT,
	val VARCHAR(30)
);

INSERT INTO #tableA
VALUES
	(1, 'abc'),
	(2, 'defg'),
	(4, 'defg');


IF OBJECT_ID('tempdb..#tableB')IS NOT NULL DROP TABLE #tableB ; 
CREATE TABLE #tableB(
	id INT,
	val VARCHAR(30)
);

INSERT INTO #tableB
VALUES
	(1,'abc'),
	(2,'def'),
	(3,'xyz');

SELECT *
FROM (SELECT id, CHECKSUM(*) chk FROM #tableA) ta 
FULL JOIN (SELECT id, CHECKSUM(*) chk FROM #tableB) tb ON ta.id = tb.id
WHERE ISNULL(ta.chk,-1) <> ISNULL(tb.chk,-2)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now