How can I drop and create a table when writing to a database ?

I have a workflow which creates a table on an Oracle SQL Server. When the workflow is re-run, I want to be able to replace the table, if it already exists. The table does not have a key as it is a table of duplicates entries, so I cannot use the Delete or Update function.
I have tried to enter "drop table tablename;" in the pre SQL section of the export settings but this doesn't seem to work.

Best Answer

Answers

  • Hayley KeareyHayley Kearey Experian Employee
    edited July 2019

    On the Export step settings, you can use the Truncate option under Advanced settings to do what you want.
    It should delete all of the rows in the target table before performing the function you select. Insert should be selected as the Mode

    You could also use the pre-SQL but this needs a specific syntax and format to work
    Thanks for your question

  • Does that mean you can only run the workflow with the create option set once, and after that you have to change the workflow to Truncate and Insert? What about if the table output were to change? Would we have to create a brand new table? That has the potential to create an entirely redundant table and, depending on what level of access the user has to that database, could create confusion, especially if the tables names correspond to the workflow.

    Is there documentation available for the pre-SQL section?

  • Thanks Hayley, that's good to know.

  • Clinton JonesClinton Jones Experian Elite
    edited July 2019

    this will be very similar to what you can do in Pandora

Sign In or Register to comment.