I have two tables with 70 columns each and they have same columns names in each table. I want to compare the data of every column and the output is number of rows which are different of every column. So far I am successful in comparing the data but I have to manually replace the column names in query instead I would like the query to pick all the columns data from both the tables and compare them and give output. Below is the query and sample output.

from NEEF_Entry.dbo.tbl_TOF
join NEEF_Entry.dbo.tbl_TOF_old ON NEEF_Entry.dbo.tbl_TOF.FormID=NEEF_Entry.dbo.tbl_TOF_old.FormID
where NEEF_Entry.dbo.tbl_TOF.StudentsClass4_15<>NEEF_Entry.dbo.tbl_TOF_old.StudentsClass4_15

output is: enter image description here The output shows how many rows in each column are different in this case 184. Sorry about bad formatting.

  • Could you please supply some sample data contained in the two tables? And possibly add the DDL of the tables? – John aka hot2use May 11 '18 at 6:46
  • Your query does not show the "number of rows", but each row for which there is a different value in the old table, together with all rows from the old table where the value for that column differs. I wonder what use your query could have. Can you explain what it is that you want to know? – Gerard H. Pille May 11 '18 at 7:09

This sounds like a job for messy, one-off dynamic SQL. You will likely want to prettify the text output and inspect the results (keeping an eye out for null comparisons), but hopefully this helps get you started.

declare @my_big_query nvarchar(max) = 'select a.MyPrimaryKeyColumn';
declare @my_big_sum   nvarchar(max) = 'select ';

select @my_big_query += 
    convert(nvarchar(max),',iif(a.'+quotename(a.name)+'=b.'+quotename(b.name)+',0,1) as '+quotename(a.name+'_IsNotEqual'))
from sys.columns a
join sys.columns b on a.name = b.name
where a.object_id = object_id('dbo.tbl_TOF')
    and b.object_id = object_id('dbo.tbl_TOF_old');

select @my_big_sum += 
from sys.columns a
where a.object_id = object_id('dbo.tbl_TOF')

select @my_big_query += convert(nvarchar(max),'
into #cached_results
from dbo.tbl_TOF a
join dbo.tbl_TOF_old b on a.MyPrimaryKeyColumn = b.MyPrimaryKeyColumn;')
    ,@my_big_sum += 'a.MyPrimaryKeyColumn from #cached_results a;'

print 'drop table if exists #cached_results;'
print @my_big_query
print @my_big_sum
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.