SQL Query Incremental data load

Neils2401 

If you're not maintaining the reference timestamp outside of the source table, and instead, you're going to use the target table to determine a reference date, then you would leverage a subquery to pull the maximum value contained in either createdDate or updatedDate in that target table.

This would look something like this:

SELECT
	[s].*
FROM
	[sourceTable] AS [s]
WHERE
	COALESCE([s].[updatedDate], [s].[createdDate]) > (
		SELECT
			CASE
				WHEN MAX([t].[updatedDate]) >= MAX([t].[createdDate]) THEN MAX([t].[updatedDate])
				ELSE MAX([t].[createdDate])
			END
		FROM
			[targetTable] AS [t]
	);

Nothing changes prior to the original COALESCE, you're just changing the WHERE statement after the relational operator.

Cheers,

Lain