troubleshooting Question

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

Avatar of tommym121
tommym121Flag for Canada asked on
Microsoft SQL Server
5 Comments1 Solution298 ViewsLast Modified:
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
                   }
        }
}

}
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros