Compare data between two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare data between two tables

Author

Topic

kvnbabu
Starting Member

2 Posts

Posted - 2009-12-07 : 07:27:51

Hi,

I have a requirement where I have to compare data between two tables and write the result into a different table. Let me explain you clearly:

1. I have a master table available in different server and have another table locally with same structure.
2. There is another master that will have list of columns that are to be excluded from check.
3. I have to write a stored procedure that will get 'Master DB Connection' and 'table name' as input parameters.
4. Read all column names from Master table Name, read column names the 'exclude table' and remove these columns from list of columns.
5. Now for each of records between Master Table & Local table and for each of column, compare the data and write to another if there is difference.

Let me also explain you by giving sample data

Master table (Master.Table1):
--------------
ID Col1 Col2 Col3
1 A B C
2 P Q R
3 X Y Z

Exlcude field data table in Master:
--------------
Table_Name FieldName
Table1 Col3

Local Table (Local.Table1):
--------------------------
ID Col1 Col2 Col3
1 A B C
2 P S T
4 X Y Z

The Result table must be:
-------------------------
ResultType Table Key Column MasterValue LocalValue
Diff Value Table1 2 Col2 Q S
OnlyMaster Table1 3 NULL NULL NULL
OnlyLocal Table1 4 NULL NULL NULL

Could anybody please help writing stored procedure for the same?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 07:37:51

Can you show us what YOU have done so far and where your problem is?

No, you're never too old to Yak'n'Roll if you're too young to die.

kvnbabu
Starting Member

2 Posts

Posted - 2009-12-07 : 08:05:33

Thanks for the reply. I am totally new to SQL Server and I am learning so I have not done anything so far but I have some thought about logic/approach in mind and here it is:

1. Define two table variables.
2. Connect to Master table using connection string and get the data master into local table variable and also the exclude field data
3. Using information_schemas, get all list of column names into another table variable
4. Using except command, remove the unwanted column names from step 3
5. for each of the value from 4th step, loop through and compare the value. If there is difference, using insert, write to another table.
6. repeat the step 5 and until all the columns data is complete.
7. There will another loop that loop through for all records in table.

Please feel free to correct me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources