What are the challenges you face when working across database platforms? Take the survey
Options

Where Clause Help Needed!

CoastalDataCoastalData Posts: 22
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

Comments

  • Options
    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!
  • Options
    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.
    Chris
Sign In or Register to comment.