Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

How to use MERGE statement with batches?

studia9999
Padawan

I am trying to use MERGE statement together with batches. I use executeBatch method from java.sql.Statement class.

This is my query:

MERGE INTO "TAB1" T USING (SELECT ? "C1", ? "C2" FROM DUAL) S ON (T."C1" = S."C1") WHEN MATCHED THEN UPDATE SET T."C2" = S."C2" WHEN NOT MATCHED THEN INSERT VALUES (S."C1", S."C2")

 And this is the error:

SQL error: Feature not supported: Prepared statement with multiple row count results

I found this answer in kafka-connect-jdbc-exasol repo: https://github.com/exasol/kafka-connect-jdbc-exasol/issues/5#issuecomment-738015285

If batches are not supported for MERGE statement then how can I merge more than 1 row at the same time?

2 REPLIES 2

exa-Aleksandr
Team Exasol
Team Exasol

Hi @studia9999 ,

Maybe you could INSERT a batch or a few batches to a transient temporary table and then MERGE it?

PeterK
Xpert

Hi @studia9999 ,

Unfortunately I don't think there's a way to use placeholders ( ? ) directly in a merge. There is an Idea requesting this. feature.

What we do for multi-row MERGE is:

1) For small data sets we embed the values directly:

    MERGE INTO ... USING (VALUES (1,'a'),(2,'b'),...) AS u (c1, c2) ON ...

2) For large data sets we use a MERGE + IMPORT clause:

    MERGE INTO ... USING ( IMPORT INTO (c1 INT, c2 CHAR(1)) FROM CSV AT ... ) AS u ON ...

Alternatively you can do as @exa-Aleksandr suggests using a staging table:

   INSERT INTO tmp VALUES (?,?)  +  MERGE ... USING tmp ... + TRUNCATE tmp

I don't know if Kafka supports any of these solutions well.

Regards,

Peter