Where Clause Help Needed!

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Where Clause Help Needed!

Postby CoastalData » Sun Aug 09, 2009 8:00 pm

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: Select all
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
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby CoastalData » Sun Aug 09, 2009 8:30 pm

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!
CoastalData
 
Posts: 16
Joined: Thu Jun 18, 2009 9:03 pm

Postby Chris Auckland » Tue Aug 11, 2009 11:06 am

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: Select all
session.CompareDatabases(db1, db2, mappings, SessionSettings.Default ^ SessionSettings.IncludeRecordsInTwo);


I hope this helps.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests