Question: Output Tool - Oracle Bulk - Appending Option

Question

Hi,

Has any one tried using a normal output tool with an OCI Bulk DCM connection to append data but not "Delete and Append" option?

The alternative to use Write Date In-DB tool, would that be writing record by record?

Issue is using the "Write Date In-DB tool" seems to take a long time when there are more than 5000 records, but I cannot find an option to check what type of writing process it follows.

The "Output" tool on the other hand when set up with "Data Sources> Oracle > Bulk" seem to only have options to:

* Delete Data & Append
* Create New Table
* Overwrite Table

Are there any options to "Bulk" write but not overwrite a table and only append to the existing data inside it?

Thanks!

VinodKammula28 · Answer

Here’s the structure of your Alteryx workflow

[Input Data]
↓
[Select / Formula (if needed)]
↓
[Output Data Tool (OCI Bulk to staging_table)]
├── Pre-SQL: CREATE TABLE IF NOT EXISTS
└── Post-SQL: INSERT INTO target_table + TRUNCATE

VinodKammula28 · Answer

Post-SQL Statement

This appends the data and optionally clears the staging table:

BEGIN
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM staging_table;

TRUNCATE TABLE staging_table;
END;

VinodKammula28 · Answer

Pre-SQL Statement (optional) This will create the staging table only if it doesn’t exist

BEGIN
EXECUTE IMMEDIATE '
CREATE TABLE staging_table AS SELECT * FROM target_table WHERE 1=0
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
END IF;
END;