Question: Output Tool - Oracle Bulk - Appending Option

[NazK]

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:

1. Delete Data & Append
2. Create New Table
3. Overwrite Table

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

Thanks!

[Vinod28]

1. Write your data to a temporary or staging table using Bulk Load.

   1. Use a Post-SQL statement to append it to your target table:     INSERT INTO target_table SELECT * FROM temp_table;
      Optionally, drop or truncate the temp table after the move.
      Oracle Bulk Loader - Alteryx Community

[Vinod28]

Automating Staging Table Creation in Alteryx

Use Pre-SQL and Post-SQL options in the Output Data Tool

Output Tool – First Write to a Staging Table

• Output to Staging_Table

• Use "Delete Data & Append" or "Overwrite Table"

• Connection type: OCI Bulk (DCM)

[Vinod28]

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;

[Vinod28]

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;

[Vinod28]

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