Insert/Update in JDBC stage
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.
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.
Asked: 2019-03-13 13:42:25 -0600
Seen: 1,123 times
Last updated: Mar 18 '19
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.
can we use this now in 3.13? [Pat Patterson's feedback] Another option would be to allow the user to specify the query format as used in PreparedStatementCache.generateQuery() - then they could say something like INSERT INTO ${TABLE} ("${COLUMNS}") VALUES (${VALUES}