In this article

1 Overview
- 1.1 Snap Type
- 1.2 Prerequisites
- 1.3 Supported Versions
- 1.4 Support for Ultra Pipelines
- 1.5 Limitations & Known Issues
2 Snap Views
3 Snap Settings
4 Examples
- 4.1 Merge data into a table
5 Snap Pack History

Overview

You can use this Snap to execute a SQL Merge with the specified properties.

The merge condition can only use variables; it does not support constants and pipeline parameters. For example SALARY =$SALARY (here, we use the SALARY variable of the input document). However, if you use SALARY = '100000' (the Snap cannot process this condition, because the provided columns in the condition are validated against the types defined in the table schema). You can rewrite this condition by using an upstream Mapper Snap, where '10000' is mapped to the SALARY variable, which then can be used in the Merge condition as defined in the earlier example. The same applies to pipeline parameters, which should be mapped upstream similarly using a Mapper Snap.
Merges are batched until the account's batch size property or until the structure of the update statement changes. An update statement changes if an incoming document contains different keys than a previous document.
While the expression enabler is available on the Merge condition field, we recommend you to minimize the usage of the expression enabler for the Merge condition field.

Snap Type

SQL Server - Merge Snap is a Write-type Snap that executes an SQL Server merge.

Prerequisites

None.

Supported Versions

This Snap supports SQL Server 2008 or higher versions.

Support for Ultra Pipelines

Works in Ultra Pipelines.

Limitations & Known Issues

None.

Snap Views

Type	Format	Number of Views	Examples of Upstream and Downstream Snaps	Description

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input

Document

Min: 1
Max: 1

Mapper

The documents that are provided in the input view will be merged into the provided table on the provided database. Each document on the input view is expected to be merged or created with/as a new record in the table.
The input view schema is provided to an upstream Mapper Snap, based on the selected service object.

Output

Document

Min: 0
Max: 1

Mapper

If an output view is available, then the original document that was used to create the statement will be output with the status of the merge executed.

A document which represents the status of the merge operation for that document. Each merge operation is represented as one document in the output view.

Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.
Discard Error Data and Continue: Ignores the error, discards that record, and continues with the remaining records.
Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

Asterisk (*): Indicates a mandatory field.
Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.
Expression icon (): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.
Add icon (): Indicates that you can add fields in the field set.
Remove icon (): Indicates that you can remove fields from the field set.

Field Name	Field Type	Description
Label* Default Value: SQL Server - Merge Example: Load Employee Tables	String	Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.
Schema name Default Value: N/A Example: schema_demo	String/Expression	Specify the database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all tables names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values. The values can be passed using the pipeline parameters but not the upstream parameter.
Table name* Default Value: N/A Example: employees_table	String/Expression	Specify the table to execute the merge on.
Number of Retries Default Value: 0 Example: 3	Integer/Expression	Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.
Retry Interval (Seconds) Default Value: 1 Example: 10	Integer/Expression	Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.
Merge condition* Default Value: N/A Example: SALARY =$SALARY	String/Expression	Specify the ON condition of the merge statement. A boolean-type expression is expected. See Merge Syntax for an explanation of merge syntax.
Identity column Default Value: N/A Example: EmployeeID	String/Expression	Specify the table's identity column. If this is not set and the column is set to be generated always, then the merge will fail.
Enable identity insert Default Value: Deselected	Checkbox	Select the check box to insert values from the input document into the target table identity column. Ensure that the target table contains an identity column. If you do not select the check box, then the Snap strips any value that is meant for the identity column from the input document.
Default Value: Validate & Execute Example: Execute only	Dropdown list

Examples

Merge data into a table

The following example pipeline demonstrates how to merge data into a specific table using the SQL Server Merge Snap.

In this example, two documents with customer information from upstream Snap are passed to the downstream Snap.

Configure the JSON Generator Snap to pass customer-related data (input data) as shown below.

Configure the SQL Server - Merge Snap as shown below. The customer data needs to be merged into a table called customer1. One of the documents has the data of existing customers with an updated 'phoneno' field and another document is of a new customer.

Once the Merge Snap execution completes, the record with customername 'John' is updated with phoneno, and a new record is created for customer 'Shaun'. The output is as follows:

SnapLogic Documentation

SQL Server - Merge