Databases & Data Warehouses - Insert and Upsert Operations
In this document, learn about Nexla's available loading options for setting up a data flow to send data to a database or data warehouse.
1. Sending Data to a Database/Data Warehouse
Data is stored in databases and data warehouses for a wide variety of workflows, and these diverse use cases require different data loading methods for creating and updating tables in the database/data warehouse.
With Nexla, users can easily set up data workflows that deliver data to database/data warehouse destinations through INSERT
, UPSERT
, or TRUNCATE+LOAD
operations to suit any use case. The following sections provide more information about these loading options, as well as instructions for setting up each mode when creating a destination.
2. INSERT
The INSERT
operation is used to add one or more new records to a database/data warehouse table. This update mode configures Nexla to create a new row in the selected table for each record sent, with the columns in these rows containing the record attribute values.
To send data to a database/data warehouse using the INSERT operation:
After completing the initial steps of setting up the database/data warehouse destination, in the
screen, select
from the Update Mode pulldown menu on the upper left side of the screen.
3. UPSERT
For existing tables in a database/data warehouse, the UPSERT operation can be used to load new data that at least partially corresponds to records that have been previously added to the table. The UPSERT update mode configures Nexla to update existing records based on an identifier the primary key and to create new rows for any records that do not correspond to a primary key value that already exists in the table.
To send data to a database/data warehouse using the UPSERT operation:
After completing the initial steps of setting up the database/data warehouse destination, in the
screen, select
from the Update Mode pulldown menu on the upper left side of the screen.
Optional: When upsert mode is selected, by default, Nexla is configured to allow table columns to be updated with Nexset records containing null values. To omit null record values from the upsert and allow records to be partially upserted, uncheck the box next to "Allow column updates with nulls".
Select the attribute that should be referenced as the primary key when performing the UPSERT operation by checking the box for that attribute in the Primary column.