topic Re: How to only insert record which is not exist in database? in Talend Studio

How to only insert record which is not exist in database?

Anonymous — Fri, 12 May 2017 22:21:09 GMT

I have a table in db2. Three fields are combined as a key. But this key only for certain data in the table. So I cannot set this key in database.

But I would like to set this key in t(DB)output component for insert. I only want the records which is not existing in the table can be inserted. If it exists, no update.

I know update/insert will work if I update the record. But I won't update record.

I also tried just insert, but it insert all records (exist or not) since I couldn't add key in database.

Do you know if there is a better way to do this?

Thanks!

Re: How to only insert record which is not exist in database?

vapukov — Sat, 13 May 2017 02:37:27 GMT

Without indexes - it always extremely Bad, so seriously talk with DBA for add Primary Key or at least indexes for this columns

But You have 2 choices, depending from how big Your data New and in Table

choice 1) create 2 Flows - new data and original table, than in tMap make a INNER JOIN and INSERT rejected rows back into table

choice 2) create stage table in DB2, insert all rows into stage table. Run SQL command for INSERT into main table only new data , truncate (delete) stage table at the end

choice 1 good when new data regular have a lot of rejected rows and size of new data +- similar with size of main table, and also could help if no indexes on table

choice 2 good when main table huge and new data regular add "small" (if compare) number of new records

DB2 good database, but without indexes it could take a ages in both case

Re: How to only insert record which is not exist in database?

Anonymous — Sat, 20 Jan 2018 10:22:40 GMT

Echoing what vapukov said in his intro sentence about DBAs - if you have a table that "a key only fits part of" you possibly have two tables and might consider splitting it into "those rows that do" and "those rows that don't". You can then have one set completely keyless so open to always inserts and the other half subject to a well formed key so properly open to update and insert.

i.e. possibly trying to use an integration tool to 'paper over' a flaw in database design.

If that is true it will not end well for either your or the too as you will be fighting against how things are ordered on a continual basis, including further steps such as adding a consuming application on top. Only you would know whether the above applies in your specific case of course... Advice on a forum can only be general on something like this.

Re: How to only insert record which is not exist in database?

Anonymous — Wed, 21 Feb 2018 07:14:49 GMT

choice 2) create stage table in DB2, insert all rows into stage table. Run SQL command for INSERT into main table only new data , truncate (delete) stage table at the end

Hi Sir,

As you mentioned choice 2, what is command for INSERT into main table only new data. Could you please mention the INSERT command for getting only newly added records from the table which contain historical data.

Regards

Amar

Re: How to only insert record which is not exist in database?

vapukov — Wed, 21 Feb 2018 07:49:12 GMT

insert into table2 select * from table1 where not exists (select * from table2 where table2.PK = table1.PK)

PK - is Primary Key column, but could be used any conditions