UPSERT Condition Screen (Magic xpi 4.9)
Use the UPSERT Condition screen to enter an UPSERT/MERGE condition that will be sent to the database to be used in the UPSERT operation. The UPSERT/MERGE statement enables you to update the record if it exists in the Database otherwise insert the same.
The UPSERT Condition screen contains the following fields:
|
|
|
|
Available Columns
|
A list of all the columns in the tables that you selected. Double-click on a column to add it to the UPSERT Condition text field.
|
|
Variables
|
A list of variables that you can use. Double-click on a variable to add it to the UPSERT Condition text field.
|
|
UPSERT Condition
|
Displays the UPSERT Condition according to the items that you selected in the Available Columns and Variables fields (above). This text should not exceed 10000 characters.
The Wizard will generate the actual UPSERT/MERGE statement on the Summary screen as per the Database type selected in the Resource.
|
To Use the UPSERT Condition Screen:
-
Create an UPSERT statement by double-clicking the required Available Columns and Variables. The selected items will be entered in the UPSERT Condition text field in the order you select them.
-
When you have entered the information, click Next. This will take you to the Summary screen.
|
|
When working with UPSERT conditions, you should be aware of the following:
-
You need to add single quotes around selected Alpha variables for UPSERT operation.
-
To specify a dynamic variable with a value that can be specified in the mapping, show that the database_field=<!?variablename?!>. For example, order: OrderID=<!?MyOrderNumber?!>. This will cause the MyOrderNumber variable to appear in the Data Mapper Destination as a part of the UPSERT composite element.
-
In scenario where a resource has DBMS type other than Oracle or Microsoft-SQL server, on selecting UPSERT property set to Yes, appropriate error will be displayed on the Data Mapper tree. At this point, the wizard will not generate any UPSERT statement.
-
It is not recommended to update the columns used in the UPSERT condition. You can manually delete those columns from the SQL statement.
-
MSSQL allows the user to update the fields which are included in the UPSERT condition in the UPDATE part of the Database schema whereas Oracle database doesn’t support it and gives an error at runtime.
|
