Write Data In-DB Tool
Write Data In-DB Tool
Use Write Data In-DB to create or update a table directly in the database.
In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, visit In-Database Overview.
Configure the Tool
Output Mode: Select the appropriate option for writing the data. Choices include...
Append Existing: Appends all the data to an existing table. The output consists of Records Before + Records After.
Delete Data & Append: Deletes all the original records from the table and then appends the data into the existing table. Note that this mode is different depending on the database you write to:
Oracle Databases: Uses DELETE statement.
SQL Server Databases: Uses TRUNCATE TABLE statement because this is a more efficient method. You need either the ALTER table or TRUNCATE permissions on the table.
Overwrite Table (Drop): Completely drops the existing table and creates a new one.
Create New Table: Creates a new table. It does not overwrite an existing table.
Create Temporary Table: Uses a CREATE TABLE statement to create a table that will be removed by the Alteryx engine upon workflow completion and requires CREATE TABLE permissions for all data sources except SQL Server. For SQL Server, a temporary table is created. This is necessary to use the data downstream as most databases don’t allow temporary tables to persist beyond the end of the session. If this option is selected, the Table Name field is disabled and displays “[a unique temporary table name is generated on each run]".
Update Rows: Updates existing rows in a table based on the incoming records.
Delete Rows: Deletes existing rows in a table based on the incoming records.
Table Name: Enter the name of the database table to create or update.
Append Fields Mapping (or SET Field Map when Update Rows is the selected Output Mode): This area becomes active when Append Existing or Delete Data & Append is chosen above.
Choose a configuration mode:
Auto Config by Name: Aligns fields by field name.
Auto Config by Position: Aligns fields by their field order in the stream.
When Fields are Different: Select how to handle nonconforming data fields from the options in the dropdown menu.
Error - Stop Processing: Throws an error in the Results window and ends the processing.
Output Applicable Fields: Applicable fields are included. Null values populate empty fields.
Options: this area becomes active when Update Rows or Delete Rows is chosen as the Output Mode.
WHERE Field Map: This forms the conditional statement for the Update Rows and Delete Rows Output Mode.
Limitations
Update/Delete Output Options currently do not support column aliases.
Update Output Option currently requires all field names to match the target table's field names, for the Auto Config by Name Append Fields Mapping option, and the number of fields to match the target table's number of fields, for the Auto Config by Position Append Fields Mapping option.
Update/Delete is currently only supported for SQL Server ODBC connections.
Update/Delete Output options currently do not support updating or deleting Null values. The comparison operators (such as
=or!=) being used for Alteryx update and delete queries return “unknown” for null values. For more information, see Microsoft SQL Server documentation.When appending data using the Write InDB tool, all columns in the target table have to be included in the INSERT statement in order for the INSERT to be valid. Missing columns will be added with a NULL value.
Query Structure
Sample queries for the Update and Delete Rows options for the Output Mode:
Update Rows
WITH upstream AS ( ... ) UPDATE t SET t.col2 = (SELECT col2 FROM upstream u WHERE u.col1 = t.col1 AND ...), ... FROM target_ t WHERE EXISTS ( SELECT * FROM upstream u WHERE u.col1 = t.col1 AND ... )
Delete Rows
WITH upstream AS ( ... ) DELETE t from target_ t WHERE EXISTS ( SELECT * FROM upstream u WHERE u.col1 = t.col1 AND ... )