SQL Server Enterprise Stage - Reject Records

chandarun · Post by **chandarun** » Wed Aug 13, 2008 2:45 am

Hi Folks,

I have installed Datastage v8 on Windows platform. I have kept the backend as SQL Server 2003. I am currenlty trying to insert/update records into the table. Please find below the scenario :

1) A new record is to be inserted if the Primary key column has a new incoming value( this is the usual Insert statement)
2) When an update has happened on the non-key fields of the table, another record is to be written into the same table with the updated values( I guess the 2 could be tracked using timestamp fields).
3) If the same Input record comes in without any change to any of the key or non-key values, the record should error out to an Error table.
The error table should also have records that have Incorrect value in the respective fields(NUll, Ddatatype mismatch etc...)

I have heard and Implemented Error tables concept in Teradata and Oracle. Can anybody guide me through this concept (maintaining history in SQL Server tables) ?

Thanks,
Chandru

ArndW · Post by **ArndW** » Wed Aug 13, 2008 3:16 am

This would be an ideal place to use the type of functionality implemented in such stages as "Change Capture", "difference", or "Slowly Changing Dimension".

chandarun · Post by **chandarun** » Sun Aug 17, 2008 11:32 pm

Thanks ArndW...can you please expand on your reply. It will be great if you could give me an example.

Thanks,
Chandru

ArndW · Post by **ArndW** » Mon Aug 18, 2008 2:11 am

Chandru - expanding on the reply isn't going to help much in this case, there are a number of ways to go about what you are intending to do.

If you were to look into the documentation and read the chapters for the stages listed above you will get a better idea of what options are available to you. Once that is done, we can discuss specific implementation options.