Great Idea! Thanks Phil. This would work if they were loading a full set of data each day, but unfortunately its an incremental load, meaning 24 hr of data...

I know of no other way.

This will run like a slug (because no indexes can be used), but try

...
WHERE
CAST(RecvdDate AS Date)...

Any reason for not changing [RecvdDate] to a Date?

Here is what I found. db2 - account_no - data type - decimal sql - account_no - data type - decimal in lookup, i have to use both columns...

Don't remember reading about that in BOL!

I called it a daily file in my hypothetical scenario of one that has the same structure of the tables. Sorry if that was not clear or got lost...

Did you do your research into the new cardinality estimator? See here, for example.

I understand the concept but not how it applies to the situation I'm working with. Suppose today I create a new target table with the VersionCol field you explained...

Have a look at this link for a far better example than mine!

It's not the same, see the following example:

DROP TABLE IF EXISTS #Source;

CREATE TABLE #Source
(
    CrapId INT PRIMARY KEY CLUSTERED
   ,VersionCol ROWVERSION NOT...

• This reply was modified 7 years, 1 months ago by Phil Parkin.

Please do not cross-post. Answers here, please.

Phil - From what I'm reading about RowVersion, that could be a potential solution going forward but won't help looking back at all of the archived tables to determine...

When thinking about it a little differently I have another question. Suppose a daily file with the same structure (300 columns, 3 million rows, and no last modified date...

Are you using an OLEDBCommand task to do this? These are notoriously slow, because they process one row at a time, and I recommend that you re-engineer this as follows:

...