Link to home
Start Free TrialLog in
Avatar of tommym121
tommym121Flag for Canada

asked on

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

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
                   }
        }
}

}
Avatar of tommym121
tommym121
Flag of Canada image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of vasto
vasto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Thanks
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