DELETING OR UPDATING DATA WITH DBOUTPUT

oldforum

• July 16, 2007 00:00

• Answered

Follow

Hi !

I want to transfer data from a table (table A) to another table (table B : same metadata) on other Oracle instance.

I use DATAINTERSECTION component to :
- only insert new rows from table A to table B
- updating rows of table B which haved changed
- deleting rows of table B which have been deleted in table A.

How can I delete or update rows with cloverETL and with DBOUTPUT component ?

Thanks a lot.

• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  Hello !
  
  DBOutputTable can taky any SQL/DML command - including delete.
  
  Use "sqlQuery" or "sqlCode" to provide something like this:
  "delete <yourtable> where <key_field>=?"
  
  And use "cloverFiedls" to select which field (its value) from Clover should be used in place of the "?".
  
  See this simple example:
  --------------------------------
  <Node id="DB_OUTPUT_TABLE0" type="DB_OUTPUT_TABLE" cloverFields="Country" sqlQuery="delete from city_country where CountryName = ?" commit="10" dbConnection="DBConnection0" />
  <Node id="DELIMITED_DATA_READER_NIO0" type="DELIMITED_DATA_READER_NIO" fileURL="D:\CloverShow\city_country.txt" />
  <Edge id="Edge1" fromNode="DELIMITED_DATA_READER_NIO0:0" toNode="DB_OUTPUT_TABLE0:0" />
  --------------------------------
• 

  ntmoyer

  • August 07, 2007 13:50

  0

  Comment actions Permalink

  do the fields passed to the cloverfields need to be in the order of the ?'s or does it matter?
• 

  avackova

  • August 09, 2007 09:36

  0

  Comment actions Permalink

  you can use fieldMap attribute to specify the exact mapping between clover and db fields

Please sign in to leave a comment.