Upsert Overview

An “Upsert” is a service that can be used to run a SQL update or insert statement. This means it can either run an Update or an Insert SQL statement depending on whether the Key (Where Clause) used found any records. If no records were found to update, an insert is done instead.

The benefit of using an Upsert to an ExecuteUpdate (another service that can be used to run an update and insert statement), is that you can enter the desired table, where clause and values, and EASYProcess will build the SQL statement for you. It will decide whether an update or an insert is required. This also does not require that the developer be very familiar with the SQL syntax.

Upsert services build the query for you given the table, where clause, and values. It follows one of the following patterns:

UPDATE [Table] SET [Column1]=[Value1], [Column2]=[Value2], …

WHERE [WhereClause]

INSERT INTO [Table] ([Column1], [Column2], ...)

WHERE ([Value1], [Value2], ...)

The drawback of the Upsert service is that EASYProcess will always use one of these patterns and requires that the WhereClause be predefined in the Keys. Sometimes a developer would like to build the update/inquire statement based on XSLT functions. A developer also might just feel more comfortable writing the statement themselves which gives more control. In these examples an ExecuteUpdate service might be a better fit.

Since EASYProcess is in charge of maintaining the query pattern in an Upsert service, it is able to do things for us, like parameterize the query.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks. These can happen when a value used in the query comes from the page in user entered data.