DSXchange

Hi,

I am running DataStage 8.7 on Linux environment.
I have a requirement to extract data from Oracle and load into a table named "table1" in SQLServer. But before loading "table1" i am required to delete all the existing records and then insert the new ones.
Tricky thing, is "Delete and insert" should be part of a transaction. i.e.
If "If insert fails, then delete should be rolledback".
How can a make both Delete and Insert as part of single transaction?
Appreciate your responses

What stage are you using? Typically you would get everything in a 'transaction' simply by using a single target stage with multiple input links.

If you need to delete all the records only when all the records are inserted then:
First insert all the records into table2 (create this temp table as before sql statement)
have the after sql statement to truncate table1 and rename table2 to table1

Delete is part of Before SQL and inserts are part of SQL statement. Both are two different transactions. I am using ODBC stage.

Not going to work that way, the deletes will be committed and then your inserts will start. They both need to be 'inside' the stage to be in the same transaction and that requires two links. The delete link needs to be ordered to run first and only needs to send 1 row to trigger the deletes.

Which ODBC stage is this? Seem to recall there being more than one, ODBC Enterprise and ODBC Connector come to mind.

If you have the grants to drop/rename tables and that kind of thing won't freak out your DBA, you can consider what yugee suggested. No need to truncate, however, drop and rename should do it.