How to make an "upsert" of records to a database using adapter service

[Mikael_Lund]

Hi

I need to add a record to a table using an adapter service in webMethods. If the record is already there it should be UPDATED and if it is not there it should be INSERTED (I guess some people call it an UPSERT).

How can I do that using the adapter service? Does the INSERT work as required above or must I use a “CustomSQL” and if so, what should the SQL statement be in this case?

Kind regards Mikael

[fml2]

You should use the “Custom SQL” adapter type for this and use the syntax that is supported by the DB you use. The syntax varies slightly depending on the DB vendor.

We used that for Sql Server, it worked without any problems.

INSERT just does an insert, this is not what you want.

[Mikael_Lund]

Thanks so much for the answer. We use Microsoft SQL Server.

Is there an “UPSERT” command I can use or do I have to make some logic like this:

IF NOT EXISTS (SELECT * FROM dbo.Table1 WHERE ID = @SomeID)

INSERT INTO dbo.Table1(Col1, ..., ColN)
VALUES(Val1, .., ValN)

ELSE

UPDATE dbo.Table1
SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
WHERE ID = @SomeID

Kind regards

mikael

[fml2]

This question is not wM specific.

See e.g. MERGE (Transact-SQL) - SQL Server | Microsoft Docs.

[Simon_Doebereiner]

Hi Mikael,

if you have access to the DB server, my personal recommendation would be a stored procedure for the upsert which can be called by the jdbc adapter.

Simon

[Dave_Pemberton]

Great answers Simon/fml2.

Typically the best way to implement DB logic is on the DB
(but that does require the right level of access)

A stored procedure to upsert masks all that DB logic from the integration, and contains this within the DB where it belongs. Of course, there are many scenarios where you don’t have the right access, or doing this could affect a support policy of a particular application, so in those scenarios you can try to build a SQL query to do this, or as a very last resort, combine individual adapter calls to retrieve/insert/update, creating an upsert service from these, and using this versus the adapters direct.

It’s typically good practice to create a service façade layer in front of the adapters and use these from your integrations versus calling the adapters directly as this can help to shield your integrations from adapter changes/etc which can happen at times, and help to alleviate the ripple effect of such changes.

It also means that for example, should you move away from direct DB integration to REST APIs, the façade can just be changed to invoke the REST APIs and the integration logic would continue to wokr.

[fml2]

A strored procedure just for the upsert? IMO it would be an overkill. I’d only use a SP if there is some non trivial logic that can’t be easily expressed in one SQL statement.

[Mikael_Lund]

Hi

I solved it like this:

First try to make an update. If that gives me 0 in “response” (no records updated) I make an insert.

[fml2]

If you tx volume is low this might/will work. But, in general, this is not tx-safe.

[reamon]

As fml2 notes above, the INSERT-if-fail-then-UPDATE and UPDATE-if-fail-then-INSERT can result in race conditions in an active environment where records are actively added/changed. The first operation does what it does, then some other thread sneaks in and changes the state --the update or insert then fails. In some cases, this approach will be fine. In others, can be problematic.

fml2 shared a link for the SQL Server merge statement. Oracle also has such a statement. MERGE