SQL Server - Merge
In this article
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 useSALARY = '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 |
---|---|---|---|---|
Input | Document |
|
| 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. |
Output | Document |
|
| 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:
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 | 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 | 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 | String/Expression | Specify the table to execute the merge on. |
Number of Retries
Default Value: 0 | 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 | 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 | 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 | 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 | 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:
Snap Pack History
Related Content
Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.