May 14, 2008 at 6:18 am
Hi all,
I need to compare the rows from table1 to table2 and return the name of the column if any differences are found...
A quick example;
Table1
ID......UserName.......DOB.......Variant
1.......Joe................1/1/08....True
Table2
ID......UserName.......DOB.......Variant
1.......Bloggs............1/1/08....True
I would like to compare each field and return the name of the column with the difference (rather than the contents of the coulmn), in this example UserName.
Any help would be much appreciated.
Thanks
May 14, 2008 at 6:36 am
Hi David,
Could you please re-check your requirement because i think you need to have a primary key.(l will proceed with the assumption that it is username)
then you can pivot both tables ( i hope you are familiar with the pivot syntax)
the result will be
pivot_table1
column_name ....... value .....primary_key_column
ID 1 joe
UserName joe joe
DOB 1/1/08 joe
Variant True joe
and similliarly for table2
and then we can use this query
select t1.column_name ,t1.value
from pivot_table1 T1 inner join pivot_table2 T2
on t1.primary_key_column=t2.primary_key_column
and t1.column_name=t2.column_name
and t1.value <> t2.value
you will get both the column name as well as the mis match value.
does this solve your problem?
Braga
May 14, 2008 at 6:44 am
Try this:
selectCOALESCE (Table1.id, table2.id) as ID
,CASEWHEN Table1.id is null then 'Missing Table1 row'
WHEN Table2.id is null then 'Missing Table2 row'
ELSE'Row exists in both tables'
ENDAS TableRowsMatch
,'UserName ' +
CASEWHEN Table1.UserName = Table2.UserName then 'same'
WHEN Table1.UserName is null and table2.UserName is null then 'same'
ELSE'different'
ENDAS UserNameMatch
FROMTable1
FULL OUTER JOIN
Table2
on Table1.ID = Table2.ID
SQL = Scarcely Qualifies as a Language
May 14, 2008 at 7:57 am
Thanks guys, I will give both suggestions a try and report back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply