In this article
...
Works in Ultra Pipelines.
Limitations & Known Issues
...
None.
Snap Views
Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
Input | Document |
|
| This Snap allows exactly one input view, a document that conforms to the input view schema of the Snap. The input view schema is provided to an upstream Mapper Snap, based on the selected service object. |
Output | Document |
|
| This Snap has at most one output view. 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. | |||
...
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
The following example illustrates the usage of SQL Merge Snap. In this example, we will supply two documents from upstream having customer information. We will try to merge this data into table called 'customer1'. One of the documents has the data of existing customer with an updated 'phoneno' field and other document is of new customer.
Following is the sample pipeline:
...
Input data is passed using the JSON Generator Snap. Following is the sample data passed:
...
The merge condition is applied on field 'customername' of customer1 table. Once the merge Snap execution completes, the record with customername 'John' updates its phoneno and a new record is created for customer 'Shaun'.
...
The sample output looks as shown below:
...
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
...