Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Tony1234
Tony1234
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 160
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
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
Tony1234
Tony1234
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 160
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
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
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1083 Visits: 2009
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.

----------------------------------------------------
How to post forum questions to get the best help
bob pearman
bob pearman
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 91
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.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1083 Visits: 2009
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.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search