In this article

...

Use this Snap to perform the MERGE INTO operation on the specified table. This operation updates, inserts, and deletes rows in/from the target table based on values in a source table. You can set match conditions in the Snap based on which the merge into operation is carried. The Snap creates a table and inserts the data if the target table does not exist.

After successfully running the Pipeline with this Snap, you can use this Snap's output view to verify whether the data would be inserted correctly in the target table.

...

Info

title	SQL Functions and Expressions for ELT

You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.

...

Parameter Name

Data Type

Description

Default Value

Example

Label

String

Insert excerpt

	File Writer
	File Writer
nopanel	true

ELT Merge Into

Merge Sales Data

Get preview data

Check box

Multiexcerpt include macro

name	getpreviewdata
page	ELT Intersect

Not selected

Selected

Database Name

String

The name of the database in which the target table exists. Leave this blank if you want to use the database name specified in the Default Database Name field in the account settings.

N/A

TESTDB

Schema Name

String

Required. The name of the database schema. In case it is not defined, then the suggestion for the schema name retrieves all schema names in the specified database when you click the suggest button.

Multiexcerpt include macro

name	ME_Schema_Name
page	ELT Insert-Select

N/A

SALES

Target Table Name

String

Required. The name of the table or view in which you want to perform the MERGE INTO operation.

Info
Only views that can be updated (have new rows) are listed as suggestions. So, Join views are not included. This also implies that the Snap account user has the Insert privileges on the views listed as suggestions.

Multiexcerpt include macro

name	ME_Schema_And_Table_Names
page	ELT Insert-Select

Note

If the target table or view does not exist during run-time, the Snap creates one with the name that you specify in this field and writes the data into it. During Pipeline validation, the Snap creates the new table or view but does not write any records into it.

The new table or view thus created will not be dropped if a subsequent/downstream Snap failure occurs during validation.
Use double quotes (““) to specify the table or view name if you want to include special characters such as hyphens (-) in the name.
A table or view name must always start with a letter.
Integers and underscores (_) can also be a part of the name.
All characters are automatically converted to uppercase at the backend. Use double-quotes to retain lowercase.

N/A

SALES_ORDERS

Target Table Hash Distribution Column (Azure Synapse Only)

String/Expression

Specify the Hash distribution column name for the target table in Azure Synapse, if you configure INSERT action in the Snap.

Info

Azure Synapse needs a table to be always Hash distributed, because the MERGE INTO statement on Azure Synapse supports only Hash distributed tables and does not support other types of target tables. See Troubleshooting section for related information on an edge case in creating a table in Azure Synapse.

If the target table is created outside the Snap or if you do not use Merge Into statement, you need not specify any value in this field.

N/A

var table

Target Table Alias

String/Expression

Specify the alias name for the target table.
You can use the alias names in MERGE INTO statements, instead of using the lengthy target table names.

Info
The Snap is equipped with the ability to auto-replace the actual table names (with the alias name), if any, used in the ON clause condition, secondary AND conditions, Update Expression list, or Insert Expression list. This applies to Snowflake, Redshift, and Synapse databases.

N/A

SLS_ORDRS

Input Source Alias

String

Enter the alias name that you want to use for the source table.

Note

The alias name is optional. It is useful in cases where the source table's column and the target table's column names are the same. For example:

src.order_id = sales.order_id

In such cases, the database cannot resolve the column ambiguity and the table qualifier is required to resolve it.

N/A

src

ON Condition

String

Required. The condition based on which you want to check the values in the target table. You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

N/A

src.PROD_ID=SALES.PROD_ID

Merge-into Statements

Use this field set to specify the conditions that activate the MERGE INTO operation and the additional conditions that must be met. Specify each condition in a separate row. Click to add a row.

This field set contains the following fields:

When Clause
Condition
Action

Note
The Snap allows the following combinations of actions: INSERT UPDATE DELETE UPDATE AND DELETE UPDATE AND INSERT DELETE AND INSERT UPDATE, DELETE AND INSERT

When Clause

String/Expression/Suggestion

Specify the matching condition based on the outcome of the ON Condition. Alternatively, click Image Modified to view and select a clause from the suggestion list.

Available options are:

WHEN MATCHED: Applies the specified condition and action when the source data matches with the target.
WHEN NOT MATCHED: Applies the specified condition and action when the source data does not match with the target.
WHEN NOT MATCHED BY SOURCE: (Available only for Azure Synapse and BigQuery.) Applies the specified condition and action when the target rows do not match the source rows.

Redshift and Snowflake supports the following actions in Merge-into statements:

When clause	Action
WHEN MATCHED	UPDATE or DELETE
WHEN NOT MATCHED	INSERT

Azure Synapse supports the following actions in Merge-into statements:

When clause	Action
WHEN MATCHED	UPDATE or DELETE
WHEN NOT MATCHED	INSERT
WHEN NOT MATCHED BY SOURCE	UPDATE or DELETE

WHEN MATCHED

WHEN NOT MATCHED

Condition

String

Specify the additional criteria that must be specified.
The action associated for the specified condition is not performed if the condition's criteria is not fulfilled. It can be a combination of both source and target tables, source table only, target table only, or may not contain references to any table at all.

Having this additional condition allows the Snap to identify whether the UPDATE or DELETE action must be performed (since both the actions correspond to the WHEN MATCHED clause).

You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

N/A

TOTAL_ORDERS = 0

Action

Drop-down list

Choose the action to apply on the condition.

Available options are:

INSERT
UPDATE
DELETE

INSERT

DELETE

Insert Expression

Use this field set to specify the column in which you want to insert the data and also the values that you want to insert into them. Each column must be specified in a separate row. Click + to add a new row.

This field set contains the following fields:

Insert Column
Insert Value

Insert Column

String/Expression/Suggestion

Specify the column in which the value must be inserted. Alternatively, click Image Modified to view and select a column from the suggestion list.

N/A

PRODUCT_ID

Insert Value

String/Expression

Specify the value to be inserted for the column. This field can be an SQL expression that references the source table's columns only or none. It cannot reference the target table's columns.

You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

N/A

src.PRODUCT_ID

Update Expression - When Matched

Use this field set to add expressions whose values must be updated when the the Merge Into condition matches the source data. Specify each column to be updated in a separate row. Click to add a new row.

This field set contains the following fields:

Update Column
Update Value

Update Column

String/Expression/Suggestion

Specify the column in which the value must be updated. Alternatively, click Image Modified to view and select a column name from the suggestion list.

N/A

TOTAL_ORDERS

Update Value

String/Expression

Specify the new value to be inserted when the Merge Into condition matches the source.
This field can be an SQL expression that may reference columns in the source table, the target table, both source and target, or neither.
You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

N/A

src.TOTAL_ORDERS + 1

Update Expression – When Not Matched By Source

Applicable only for Azure Synapse and is used only when you choose the WHEN NOT MATCHED BY SOURCE as the When clause.
Use this field set to add expressions listing out columns whose values must be updated or deleted if the source rows do not match the target rows in the Merge Into operation. Click to add a new row.

This field set contains the following fields:

Update Column
Update Value

Note
If the options in this field set do not populate on choosing the WHEN NOT MATCHED BY SOURCE clause, the snap displays an error.

Update Column

String/Expression/Suggestion

Specify the column name in which the value must be updated or deleted as specified in the conditions. Alternatively, click Image Modified to view and select a column from the suggestion list.

Note
When you choose Delete action, the Snap ignores the Update Expression - When Not Matched by Source field set.

N/A

TOTAL_ORDERS

Update Value

String/Expression

Specify the new value to be inserted when the Merge Into condition matches the source.
This field can be an SQL expression that may reference columns in the source table, the target table, both source and target, or neither.
You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details.

N/A

src.TOTAL_ORDERS + 1

Preventing SQL Injection

...

Method 2: Dynamic Substitutions

You must enable expressions when using Pipeline parameters for dynamic substitutions. Format the SQL expression, except the Pipeline parameter's reference, as a string.

For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ON Condition, Condition, Insert Value, or Update Value fields:

...

In this example, we want to retrieve records from two tables. First, we transform the retrieved records using conditions and use a JOIN clause to join the queries from the two tables. We then perform transformations on the records and apply aggregate function. Finally, we merge the records by applying conditions and actions. This example demonstrates how we can accomplish this task.

First, we use two ELT Select Snaps to build SELECT queries to retrieve all records from the tables (ORDERS_34R_2KB and CUSTOMER_50R_3KB). The configuration and query output is as shown below:

ELT Select Snaps	Output

The tables have several columns. But, we want to retrieve only the CUST_CODE and ORD_AMOUNT columns. Therefore, we configure the ELT Transform Snap with the following settings, which builds the query as shown below:

ELT Transform	Output
Image Modified	Image Modified

The SELECT queries in the ELT Select and ELT Transform Snaps form the inputs for the ELT Join Snap. Now, we apply an inner join based on matching values of the CUST_CODE column in the tables. Hence, we configure the ELT Join Snap and validate. Upon validation, the ELT Join Snap combines both the incoming SELECT queries and adds the JOIN clause. The output preview is as shown below:

ELT Join	Output
Image Modified	Image Modified

We use the ELT Transform Snap to retrieve only the CUST_NAME and ORD_AMOUNT columns after joining the queries. We configure the Snap with the following setting which builds the query as shown below:

ELT Transform Snap	Output

Subsequently, we use an ELT Aggregate Snap to apply an aggregate function on the values in the ORD_AMOUNT column. Here, we want to calculate the SUM for the ORD_AMOUNT column.
Image Modified

The Snap builds the query as shown below:
Image Modified

Finally, we use the ELT Merge Into Snap to merge the records into the Target table MERGE_INTO_OUT_01 based on the ON Condition, apply Merge-into statements, update actions, and update expressions.
The ON condition checks for the customer's name - if the customer's name matches, it applies the Merge-into statements and applies conditions as configured below. In this case, if the customer's name matches and if the condition (SUM_COLUMN<5000) is met, then the Snap updates the record with customer's name. If the customer's name does not match and if the condition (SrcAlias.SUM_COLUMN<5000) is met, then it inserts the customer's name into the record.
Image Modified

Upon successful execution, the Pipeline adds and updates customer records in the Target database table in the configured database.

Download this Pipeline.

Downloads

Note

title	Important Steps to Successfully Reuse Pipelines

Download and import the Pipeline into SnapLogic.
Configure Snap accounts as applicable.
Provide Pipeline parameters as applicable.

...

Versions Compared

Old Version 52

New Version 53

Key

Preventing SQL Injection

Method 2: Dynamic Substitutions

Downloads

Page Comparison

Versions Compared

Old Version 52

New Version 53

Key

Preventing SQL Injection

Method 2: Dynamic Substitutions

Downloads