SQL - writing a generic routine to compare rows of two table using Dynamics SQL

tommym121
tommym121 used Ask the Experts™
on
I have two tables (T1 and T2) with exact same structure and ID as primary key
I use this statement (eventually I have to programatically generate it) to generate the below result
ID      T1.Name      T2.Name      T1.Birthday      T2.Birthday
1234      Richard         Richard         1971-02-09      1971-02-09
2345      IreneLee        Irene           1975-04-24      1976-04-24
3456      Danny           Danny           1962-09-08      1962-09-08
4568      VictorChu       Victor          1962-09-08      1962-09-08

And I use the below script
DECLARE @tableName varchar(max)
Set @tableName= 'T1'
Select column_name From
(
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @tableName
Except
SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND table_name = @tableName
)tmp

column_name
Birthday
,Name

I need to find out what is different between each similar column (e.g. T1.Name and T2.Name) for each row  It looks like I need two loop.  I am not familiar in SQL script. Can anyone one suggest a way to do it.  In other programming language, I would know how to structure this, but not in SQL.   Any help will be much appreciate.

This is what I try to achieve

For (row in Rows for JoinTable)
{
        For(column in Columns for ColumnTable)
        {
                   if ('T1'.column <> 'T2'.column)
                   {
                           Insert  ('T1.ID' ,  'Update T2. ' + column name, 'T1'.column, 'T2'.column)
                                       into    #ChangesTable
                   }
        }
}

}
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Sorry I forget to put the statement to create the first table.

With cte1 As
(
      Select T1.ID As 'ID',
      T1.Name As 'T1Name',
      T2.Name As 'T2Name',
      T1.Birthday As 'T1Birthday' ,
      T2.Birthday As 'T2Birthday'
      from T1 inner join T2 on T1.ID = T2.ID
)
Select *  from cte1
Top Expert 2011
Commented:
I modified your CTE sql to add a column Result, which shows the difference:

With cte1 As
(
      Select T1.ID As 'ID',
      T1.Name As 'T1Name',
      T2.Name As 'T2Name',
      T1.Birthday As 'T1Birthday' ,
      T2.Birthday As 'T2Birthday',
      CASE ISNULL(T1.Name,'')=ISNULL(T2.Name,'') THEN 0 ELSE 1 END +
      CASE ISNULL(T1.Birthday,'1/1/1900')=ISNULL(T2.Birthday,'1/1/1900') THEN 0 ELSE 2 END as Result
      from T1 inner join T2 on T1.ID = T2.ID
)

Select *  from cte1

Result column will contain 0 if no difference, 1 if Name is different, 2 if Birthday is different and 3 if both Name and Birthday are different

for example :
Select *  from cte1 WHERE Result=0
will return rows with the same data

Author

Commented:
vasto,

That is a excellent idea.  Thanks for your reply.   In my real scenario, I will have a lot more columns (over 50 column) . and a lot of more tables for the comparison. I will like to make it generic.  I can turn your solution to dynamica SQL.
However, What is the best way to examine it so I can create a change list like:
Update Name for record 2345
Update Birthday for record 2345
Update  Name for record 4568.

What do you think of having a while loop to extract the bit out from result until it become 0
Is there a better way to do that?

Would you use the bit if you know there are over 50 columns,  do you think we should  either appending the ordinal position of the columns or the column names to the result instead.  And opinion will be appreciated.  Thanks for your suggestion

Author

Commented:
Thanks
Top Expert 2011

Commented:
Tommy , I am sorry I didn't see your previous comment. 50 columns is a little bit big number and you will need to use BIGINT for the Result column. I guess you can write a cursor which will loop through the columns of the tables and add a comparisson to a dynamic @SQL string which later will be executed with exec @SQL.

decalre @Result bigint, @Counter int
select @Result=0,@Counter=0

--code to get all columns from the tables
--loop starts here

set @Result= 2^@Counter
--code to get the column name and set @CurrentColumnName
SET @SQL=@SQL + 'CASE ISNULL(T1.' + @CurrentColumnName + ','''')=ISNULL(T2.' + @CurrentColumnName + ','''') THEN 0 ELSE ' +convert(varchar,@Result) END + '

SET @Counter=@Counter+1

--end of the loop


You may need to adjust '''' in the SET @SQL row.

at the end the generated code will look like this:

CASE ISNULL(col1,'')=ISNULL(col1,'') THEN 0 ELSE 1 END +
CASE ISNULL(col2,'')=ISNULL(col2,'') THEN 0 ELSE 2 END +
CASE ISNULL(col3,'')=ISNULL(col3,'') THEN 0 ELSE 4 END +
CASE ISNULL(col4,'')=ISNULL(col4,'') THEN 0 ELSE 8 END +
CASE ISNULL(col5,'')=ISNULL(col5,'') THEN 0 ELSE 16 END +
CASE ISNULL(col6,'')=ISNULL(col6,'') THEN 0 ELSE 32 END +  ....


the value in the result column may get really big for the 50th column - that is why you should use BIGINT.

since the values aare ^2 you can use bitwise operations to identify which columns are different

for example to check if column #5 is different :
declare @ColumnValue BIGINT
SET @ColumnValue=2^4
if (@Result & @ColumnValue= @ColumnValue)
BEGIN
...
END

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial