Search This Blog

Tuesday, September 14, 2010

Find data difference between two schema identical tables

Declare @vSQL varchar(max)
Declare @vCols varchar(max)



Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)
Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)


Insert into vTable1
Select 1,123,'CS',465 Union All
Select 2,123,'CS',345 Union All
Select 3,223,'TE',190





Insert into vTable2
Select 1,123,'CS',465 Union All
Select 2,223,'TE',345 Union All
Select 3,223,'TE',190



-- Get the column names from schema with case statements to get 0 or 1 as
result


-- Now, this will depend upon the columns of your actual tables


Select @vCols = Stuff((Select ',case when a.' +
[name] + ' = b.'
+ [name] + ' then 1 else 0 end as ' +
[name] from sys.columns
where Object_id
= Object_id('vTable1') for XML Path('')),1,1,'')


print @vCols


-- Concatenate the @vCols with main sql



Set @vSQL = ' Select a.id,' + @vCols + ' From vTable1 a
Inner Join vTable2 b on b.ID = a.ID '


Print @vSQL
Exec (@vSQL)

 
Drop table vTable1
Drop table vTable2


No comments:

Post a Comment