Red Gate forums :: View topic - Usinga where to compare a value in the src and dst tables.
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Usinga where to compare a value in the src and dst tables.

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
MarkLFT



Joined: 22 Apr 2011
Posts: 10
Location: Bali, Indonesia

PostPosted: Fri Apr 22, 2011 4:13 am    Post subject: Usinga where to compare a value in the src and dst tables. Reply with quote

I am trying to sync to databases, they have identical structures, and every table has a createdOn, updatedOn, and deletedOn field.

What I would like to do is use a where clause to only update data where the src.UpdatedOn > dst.UpdatedOn.

All of the examples I can find for where clauses only seem to apply to one table, i.e. id > 3 etc.

Is there a way to filter based on a comparison of fields in both the source and destination tables?
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Mon Apr 25, 2011 2:41 pm    Post subject: Reply with quote

Hi there,

Thanks for your post. You should be able to apply a WHERE clause to both tables, and an example is viewable on the below page:

http://sdk.red-gate.com/index.php/WhereExample

HTH!

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
Back to top
View user's profile Send private message Send e-mail
MarkLFT



Joined: 22 Apr 2011
Posts: 10
Location: Bali, Indonesia

PostPosted: Thu May 05, 2011 3:49 am    Post subject: Reply with quote

Hi Peter,

Thanks for the suggestion, however I think you may have misunderstood my question. What I am looking to do is compare values within the two tables, i.e. both tables have a UpdatedOn field. I only want to update a tabe based on which which tables UpdatedOn field is the newest?

I run a sync job every minute or so, and normally the data is only changed on one place at a time. so when I run the push part i.e. DbA -> DbB, I only want to update records in the table where DbA.TableA.UpdatedOn is greater than DbB.TableA.UpdatedOn. Then a minute later it runs a Pull job i.e. DbA <- DbB, and this timne I only want to update the table in DbA is the UpdatedOn in DbB is greater than DbA.

Bother systems use a common time source, so there should not be any time differences except where data has been updated.

Many thanks

Mark
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu May 05, 2011 10:22 am    Post subject: Reply with quote

Hello,

You can use another table as the basis for a WHERE clause but you must be really careful and test the results as it may have unintended consequences.

In the "worked example" databases WidgetDev and WidgetLive, you can use a distributed query to use WidgetLive's Contacts table as a WHERE clause for the WidgetDev's contact table:
Code:

Where clause for WidgetDev:
JoiningDate > (select top 1 JoiningDate from WidgetLive.dbo.Contacts where JoiningDate > '2007-09-14 16:09:22')


This will filter all contacts from WidgetDev that have joined later than the first person who joined after 16:09:22 in WidgetLive.
Back to top
View user's profile Send private message
MarkLFT



Joined: 22 Apr 2011
Posts: 10
Location: Bali, Indonesia

PostPosted: Thu May 05, 2011 10:37 am    Post subject: Reply with quote

Hi, it may be me not fully understanding your solution, but I think this will not work.

What I am looking for is each row is compared on a one to one basis. i.e. If the row exists in Dev but not Live we copy row from Dev to Live, if the row exists in Live but not Dev, we delete it from Live.

If exists in both (same key field) , the the UpdatedOn in matching rows is compared, and if the row in Dev is newer than the row in Live, the row is copied, however if both UpdatedOne are the same or the row in Live has a newer UpdatedOn than the corresponding row in Dev, the row is not copied.

This evaluation must be done on a row by row basis, not just compared to a set value.

Thanks
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu May 05, 2011 11:15 am    Post subject: Reply with quote

Using the SDK and writing a C# program may be your only solution, then. When the tables are compared, you can use a SelectionDelegate to filter out the updates you don't want to run.
Back to top
View user's profile Send private message
MarkLFT



Joined: 22 Apr 2011
Posts: 10
Location: Bali, Indonesia

PostPosted: Thu May 05, 2011 11:18 am    Post subject: Reply with quote

I am using the SDK, but I still cannot find a way to do this.

Thanks
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu May 05, 2011 2:34 pm    Post subject: Reply with quote

You want to have a look at the selectiondelegate example. Or perhaps the filtering overview.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group