Options in Output tool

I'm trying to find more information about the Output Data tool, specifically the Output Options

2018-04-24 14_38_53-Clipboard.png

I need to know specifically how the "Delete Data & Append" option works in regards to big tables (ie 1+ million records).

Does this:

a) delete the records in the table, then add the new records, OR

b) truncate the records in the table, then add the new records, OR

c) it depends on the database (in which case I'm using Netezza at the moment)

I can't find anything this specific in the help files and the processing time does have an impact at this level and I want to avoid writing the Pre Create SQL Statement if I can.

Output

Accepted answers

KaneG

I performed a quick profile and on my SQL instance, it performed a Truncate.

anotherusername

Update:

I've done some testing with both in-DB (Write Data In-DB) and std Output tool (Output Data). Both are set to DELETE DATA & APPEND and I am getting DELETE commands being issued to Netezza.

(the 2 tools are in the same workflow, writing data from the same source, but to 2 different tables in the DB)

Have had a server admin checking the logs for me.

This soft-delete in Netezza is probably important to take note of, as it will therefore need regular GROOMs to be performed on tables with regular/large churn.

(am using 11.7 - perhaps may differ between versions?)

All comments

patrick_mcauliffe

@cmcclellan:

Delete and Append:

- The entire table's data (not the table itself/structure) will be deleted and the data feeding the Output tool will replace it.

Append Existing:

- Take the records feeding the Output tool will just be added (like a union) into the table.

Overwrite:

- Imagine it executing this SQL statement: Drop Table (TableName); Create Table (your incoming data here). This option would be used over the Delete and Append if you wanted to change the primary keys or needed to add/remove fields

cmcclellan

What I was meaning was in the "delete and append" is the "delete" really a delete or is it a truncate ?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703