Ask Your Question
0

Insert/Update in JDBC stage

asked 2019-03-13 13:43:00 -0600

divyang gravatar image

Is there a way in any Jdbc Stage where I can insert a record if it does not exist in a table and update a record if it exists in the table.

edit retag flag offensive close merge delete

Comments

This is currently not an option. Please watch this issue: https://issues.streamsets.com/browse/SDC-6472 and also read some of the potential workarounds in the comments there.

jeff gravatar imagejeff ( 2019-03-14 10:25:10 -0600 )edit

1 Answer

Sort by ยป oldest newest most voted
0

answered 2019-03-18 18:42:48 -0600

metadaddy gravatar image

updated 2019-03-18 18:43:31 -0600

There is actually a workaround for this, using the JDBC Query Executor. You can specify the 'query' to be anything. For example, I'm using a JDBC Query Executor with MySQL and a query of the form:

INSERT INTO mytable (column_pk, column_a, column_b) 
VALUES('${record:value('/pk')}',
       '${record:value('/a')}',
       '${record:value('/b')}')
  ON DUPLICATE KEY UPDATE 
    b=VALUES(b),
    c=VALUES(c)

You'll need to change the SQL according to the database that you're working with.

edit flag offensive delete link more

Comments

It helped me solved the same problem. But just to note that for postgres the "DUPLICATE KEY" should be "CONFLICT (id) DO UPDATE SET". thanks so much.

Meihong gravatar imageMeihong ( 2019-11-12 19:54:02 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-13 13:42:25 -0600

Seen: 413 times

Last updated: Mar 18