Where Clause Help Needed!

CoastalData

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:

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

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!

Chris Auckland

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.

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

I hope this helps.