Options in Output tool

[cmcclellan]

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

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.

[anotherusername]

The In-DB help file has info (even the SQL UPDATE command structure), but I can't see the same for the standard Output Tool help file.

https://help.alteryx.com/2018.4/LockInOutput.htm?Highlight=output%20data%20update

You'd *think* that the same command would be issued for both in-DB and Std Output tools, but given that they differ in options on how an Update is done then it must differ in some manner, and is hopefully documented somewhere that I haven't found yet!  (NB: when I first starting using the the tools a few versions ago the Update didn't exist as an option, so I've done workarounds (ie. read full dataset in, update existing rows, union new rows, then delete & append full dataset in output (using temp table before write if using inDB tools to avoid deleting the select from dataset!)) and so I haven't tested this newer Update option in either toolset)

However, given that this thread identified earlier that Alteryx sends different commands to different DB products (ie. TRUNCATE to Kane's sql instance (not sure of product, maybe MS?) but a DELETE to IBM Netezza), quite possibly the UPDATE structure differs - with or without some kind of documented or syntactical reason..

[hafjashpp]

Output Options	Select an output option: Create New Table: Creates a new table, but does not overwrite an existing table. Append Existing: Appends data to an existing table so that the output consists of Records Before plus Records After. Delete Data and Append: Deletes all original records from the table and appends data to the existing table. Overwrite Table (Drop): Drops the existing table and creates a new table.	.accdb, .mdb, .tde, .xls, .xlsx (via the legacy .xlsx driver), .oci, OLEDB, ODBC
Output Options	Select an option: Update, warn on failure: Updates existing records using the output and warns if a record could not be updated. Update, error on failure: Updates existing records using the output and stops processing if a record could not be updated. Update, insert if new: Updates existing records using the output and inserts new records if they were not in the database table and stops processing if a record could not be updated. If there are multiple records with the same primary key and no other SQL errors occur, the new record updates the older record in the database. Use the Unique Tool to check for multiple primary keys prior to writing to the database.

[hafjashpp]

https://help.alteryx.com/current/designer/file-format-options