tommym121
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_COL UMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(c onstraint_ 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
}
}
}
}
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_COL
WHERE OBJECTPROPERTY(OBJECT_ID(c
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
}
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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(col 1,'') THEN 0 ELSE 1 END +
CASE ISNULL(col2,'')=ISNULL(col 2,'') THEN 0 ELSE 2 END +
CASE ISNULL(col3,'')=ISNULL(col 3,'') THEN 0 ELSE 4 END +
CASE ISNULL(col4,'')=ISNULL(col 4,'') THEN 0 ELSE 8 END +
CASE ISNULL(col5,'')=ISNULL(col 5,'') THEN 0 ELSE 16 END +
CASE ISNULL(col6,'')=ISNULL(col 6,'') 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
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(col
CASE ISNULL(col2,'')=ISNULL(col
CASE ISNULL(col3,'')=ISNULL(col
CASE ISNULL(col4,'')=ISNULL(col
CASE ISNULL(col5,'')=ISNULL(col
CASE ISNULL(col6,'')=ISNULL(col
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
ASKER
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