Archives of the TeradataForum

Message Posted: Tue, 20 Mar 2012 @ 22:27:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Such tables need to have a (business) date opened and (business) date closed and the closed date commonly defaults to a high date such as 9999- 12-31.

Load the new extract from source systems to your staging area. Left outer join those to the target table where the closed date is high or (in more recent versions, where the record is effective).

Where the identifier from the target (right hand side of the join) is null, insert the record.

Where the identifiers and other attributes match - take no action.

Where the identifiers match but the other attributes do not, update the high value to show it is closed and insert the new record with a high date to show it is effective.

That's more than one SQL instruction so use a Macro rather than an SP.

Is that what you wanted?

Kenneth Hansen

	<Prev	Next>		<<First	<Prev	Next>	Last>>