On this Page
| Table of Contents | ||||
|---|---|---|---|---|
|
Snap type: | Write | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description: | This Snap executes a SQL Merge with the given properties. 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. This Snap supports SQL Server 2008 or newer.
The merge condition can only use variables, no constants or pipeline parameters. A good example for a merge condition is SALARY =$SALARY (here we use the SALARY variable of the input document). A bad example for a merge condition is SALARY = '100000' (this will not work since we validate the provided columns in the condition against the types defined in the table schema).
The bad example can be rewritten 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 a good example. The same applies for pipeline parameters, which need to be mapped upstream similarly using a Mapper Snap. Expected upstream Snaps: The columns of the selected table need to be mapped upstream using a Mapper Snap. The Mapper Snap will provide the target schema,which reflects the schema of the table that is selected for the merge Snap. Expected downstream Snaps: The Snap will output one document for every record merged, hence any document processing Snap can be used downstream. Expected input: 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. Expected output: Document which represents the status of the merge operation for that document. Each merge operation is represented as one document in the output view. | |||||||||||||
| Prerequisites: | None | |||||||||||||
| Support and limitations: |
| |||||||||||||
| Account: | This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring SQL Server Accounts for information on setting up this type of account. | |||||||||||||
| Views: |
| |||||||||||||
Settings | ||||||||||||||
Label | Required. 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 | The database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all table names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values.
Example: SYS | |||||||||||||
Table Name | Required. The table to execute the merge on.
Example: people Default value: [None] | |||||||||||||
| Number of retries | Specifies 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.
Example: 3 Default value: 0
| |||||||||||||
| Retry interval (seconds) | Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. Example: 10 Default value: 1
| |||||||||||||
Merge condition | Required. The ON condition of the merge statement. A boolean-type expression is expected. See Merge Syntax for an explanation of merge syntax. Default value: [None] | |||||||||||||
Identity column | Required. The table's identity column. If this is not set and the column is set to be generated always, then the merge will fail. Default value: [None] | |||||||||||||
| Enable identity insert | 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: Not selected | |||||||||||||
|
| |||||||||||||
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:
See Also
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|