Four Stars

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

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!

 

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

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

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

-----------
1 REPLY
Nine Stars

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

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

-----------