Donate to support site

We have two tables with the same structure and wish to find the differences in the popluated data
This can be used to populate a difference table to update one of the tables.

Create the test tables
            create table T1 (name varchar(20) not null primary key, address varchar(20), age int, s varchar(20));
            create table T2 (name varchar(20) not null primary key, address varchar(20), age int, s varchar(20));

Insert data -. 	Column s is a description of the test
				name is the primary key and is used to identify the row for changes
            insert T1 select 'John', 'Address1', 25, 'same';
            insert T1 select 'Peter', 'Address2', 30, 'different address';
            insert T1 select 'Harold', 'Address3', 19, 'different age';
            insert T1 select 'Gerald', 'Address4', 50, 'missing T2';
            insert T2 select 'John', 'Address1', 25, 'same';
            insert T2 select 'Peter', 'Address21', 30, 'different address';
            insert T2 select 'Harold', 'Address3', 20, 'different age';
            insert T2 select 'James', 'Address3', 19, 'missing T1';
 
Find data that is different between the two tables or row does not exist in T2
			select T1.*, T2.*
            from T1
				left outer join T2
					on T1.name = T2.name
            where	(T1.Address <> T2.Address
					or T1.age <> T2.age
                       or T2.name is null
					);

Find data that is different between the two tables or row does not exist in T1
            select T1.*, T2.*
            from T2
				left outer join T1
  					on T1.name = T2.name
            where 	(T1.Address <> T2.Address
					or T1.age <> T2.age
					or T1.name is null
					);

            drop table T1;
            drop table T2;



This version has a column indicating the reason for the difference data output
This can be used to update the two tables

Reason desctiptions
	Different between the two tables
	Row not in T2
	Row not in T2

            select T1.*, T2.*,
						case when T1.name is not null and t2.name is not null 
							then 'different' 
							else 'not in T2'
						end
            from T1
				left outer join T2
					on T1.name = T2.name
			where 	(T1.Address <> T2.Address
					or T1.age <> T2.age
					or T2.name is null
					)
			union all
            select T1.*, T2.*, 'not in T1'
            from T2
				left outer join T1
  					on T1.name = T2.name
            where 	(T1.name is null
					);