Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 GruwezOracle 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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