Update database Table with Data associated to Date that does not exist in database tableau

[Page0727]

I have data that I am uploading into a database using the DB table Creator and DB Insert nodes. However what I really want to do once the table has been created is to just add new records where the date does not exist in the current database table. I am using the following:

It seems that this may require the DB Merge node but I am unsure.

Thanks

[takbb]

Hi @Page0727 , the question to me would be are you wanting to update existing rows with your data as well as insert new items, or simply insert new items and ignore the rest?

If the answer is that you want to both insert and update, then DB Merge may be the way forward.

If you are wanting to only insert new items and ignore the rest then a different approach may be needed. For this I really see there are several options depending on your situation.

You could insert all incoming records into a “loading” table (Y) in the database and then execute a sql query of the form

insert into X (id, a, b, c) select id, a, b, c from Y where not exists (select 1 from X where X.id = Y.id)

(You may not yet have a comparable ID on your uploading data so you might need to replace the id comparison with some other comparison condition.)

Or you could delete all rows from Y where the data exists in X and then insert the remainder.

Alternatively you could read all of table X back into KNIME, join your new data to this using a JOINER to find all non-matching rows from your new data and then insert only those rows into your database.

Or you could loop through your new data and for each row check if it exists in X and perform an insert if it doesn’t. This would likely be the slowest option, especially if new data volumes are large, but also probably the simplest approach (and easiest to understand by future developers!)

There may be other solutions which are hybrids of the above.

Which of these options is practical largely depends on data volumes and the frequency the operation is to be performed.

.