How to Compare Data between to servers based in Id's

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » How to Compare Data between to servers based...

How to Compare Data between to servers based in Id's

Rate Topic

Display Mode

Topic Options

Author

Message

Tony1234

Posted Monday, February 18, 2013 6:50 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:54 AM
Points: 14, Visits: 95

Hi All,

I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass from a sql server table, I need to identify the corresponding ID related data for each column in syn or not. If the data is not in syn then I need to maintain that data with column name in another table.

Can Any one help me on the above requirement.

Thanks

Post #1421187

opc.three

Posted Tuesday, February 19, 2013 5:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 6,720, Visits: 11,756

It sounds like you are looking into writing a package that will something very similar to what redgate SQL Data Compare tries to do.

Can you please clarify these statements:

I need to identify the corresponding ID related data for each column in syn or not.

What do you mean by "for each column in sync or not"?

If the data is not in syn then I need to maintain that data with column name in another table.

Are you saying that if you compare two rows with an ID of 10 are the same but column1, column3 and column5 are not matching that you want to store 3 rows in some "audit table" that shows:

ID ColumnName
-- -------------------
10 column1
10 column3
10 column5

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1421581

Tony1234

Posted Wednesday, February 20, 2013 10:20 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:54 AM
Points: 14, Visits: 95

Thank's for you Replay.

The scenario is

Server 1:

ID Address
-------------------
1 USA
2 UK
3 IND

Server 2:

ID Address
----------------
1 Singapur
2 UK
3 IND

My goal is to get the the out of sync data from the servers

In the above example, In server1 we have value USA for Id 1 and in server2 we have value singapur.

I have the Ids based on Ids I Need to check the data.

Expected result is:

ID ValueInserver1 Valueinserver2 DiffColumnname
--------------------------------------------------------------------
1 USA Singapur Address

Post #1422397

opc.three

Posted Thursday, February 21, 2013 6:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 6,720, Visits: 11,756

What is the purpose of capturing the results in a table with the column name that differs? I am not seeing a clear and easy way to do this with the out-of-the-box components SSIS provides unless you consider writing some .NET code in Script Tasks or Components to do the comparisons. Seriously, have you had a look at redgate SQL Data Compare?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1422522

mmartin1

Posted Thursday, May 09, 2013 11:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192, Visits: 640

You could add a checksum column on to apply to the columns wher you think the changes may occur. This will identify your row Id's that are out of sync. It is not a perfect application

http://www.sqlservercentral.com/Forums/Topic776261-148-1.aspx
From there it is just a matter of writing some code to get the data in the format you want, maybe using a table variable to hold the two different values with the same ID.

Post #1451269

bob pearman

Posted Wednesday, May 15, 2013 4:01 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:31 AM
Points: 66, Visits: 81

there are loads of ways to achieve this..

How many rows of data are we talking about in the tables? if we're talking a few thousand and you are able to add a linked server then just join on the id's and add a where clause that compares the second column.

If you're not able to add a linked server, then bcp/ssis/use openrowset to copy one set of data to a temp table on the other server to do the compare.

If you have millions/billions of rows to compare then, you will need to do a few more steps, like ssis across the records, maybe in batches and you'd probably want to index the joining column, but then the tecnique is the same.

hope this helps.

Post #1453285

mmartin1

Posted Wednesday, May 15, 2013 4:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192, Visits: 640

If your goal is just to keep the two tables in sync you could just use transactional replication from the source to the subscriber (whichever server is which). if they both need to update each other, meaning the updates are not always in the same direction, you can have transactional replication with updating subscriber. If there is more than one subscriber you then can try using merge replication.

Post #1453288

« Prev Topic | Next Topic »

Permissions