Red Gate forums :: View topic - Where Clause Help Needed!
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

Where Clause Help Needed!

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



Joined: 18 Jun 2009
Posts: 16

PostPosted: Sun Aug 09, 2009 8:00 pm    Post subject: Where Clause Help Needed! Reply with quote

Hello, I have a large table full of data that I'm syncing to another database on another server.

In order to speed the transfer up, there's no need to compare every single record in the db, I only need to review records whose DMDate (Data Modified) column has been updated within the last month.

I cannot seem to create a suitable where clause for the destination database; if I use the same clause for both, then some records from the first are not found in the second, and then they are treated as new records, and then I get a primary key violation.

If I use an EMPTY clause for the second database, then the performance gain is lost.

My code currently looks like this:

Code:
mapping.Where = New WhereClause("LastEdit >= '" & varFilterDate & "'", "")


I feel like it should be checking for the existence of a record before attempting to either do an INSERT or an UPDATE, so that it is always right.

What am I missing?

Thanks in advance!

--Jon
Back to top
View user's profile Send private message
CoastalData



Joined: 18 Jun 2009
Posts: 16

PostPosted: Sun Aug 09, 2009 8:30 pm    Post subject: Reply with quote

Hmmm, big problem with leaving the second where clause blank -- it now wants to delete every record from the destination database that isn't listed in the source database!
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Aug 11, 2009 11:06 am    Post subject: Reply with quote

Thanks for your post.

I think your're going about it the right way, the only thing you need to stop is the records being deleted from the target database.

You can do this using SessionSettings with CompareDatabases e.g.

Code:
session.CompareDatabases(db1, db2, mappings, SessionSettings.Default ^ SessionSettings.IncludeRecordsInTwo);


I hope this helps.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
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