In this article
...
You can use this Snap to execute a SQL Merge with the given 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.
...
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. The values can be passed using the pipeline parameters but not the upstream parameter. | ||||||||||||
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. When you enable retry and a connection failure occurs that cannot be recovered by retry, the Snap writes a connection failure error in the error view. In some cases, the error view records might contain duplicate data that was written to the database. Ensure to verify all the records in the error file before you restart the merge request. You can locate the duplicate records in the error view by checking the $reason property for the sub-string: Connection is not available, request timed out. | ||||||||||||
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
| Expand | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
...