ODBC - delete then insert
Posted: Mon Jul 06, 2015 5:56 am
Hi Friends,
Requirement -: We are loading data into oracle database based on templated_id so before loading data need to delete recording for same templated_id.
Implement -: in ODBC stage i choose write mode - "delete then insert"
insert statement "INSERT INTO "O2K"."CERT_TEMPLATE_BINDING"("CERTIFICATE_TEMPLATE_ID","TEMPLATE_VARIABLE","FIELD_NAME") VALUES(ORCHESTRATE.CERTIFICATE_TEMPLATE_ID,ORCHESTRATE.TEMPLATE_VARIABLE,ORCHESTRATE.FIELD_NAME)"
delete statement "delete from O2K.CERT_TEMPLATE_BINDING where CERTIFICATE_TEMPLATE_ID=ORCHESTRATE.CERTIFICATE_TEMPLATE_ID"
Status - It's working fine but problem with data.
For example- CERTIFICATE_TEMPLATE_ID=449 i have 40 records in CERT_TEMPLATE_BINDING table so when i will run the job first it will delete all 40 records then insert first node records
(suppose we have total 50 records then it will insert first node data as 25 records) and then again execute delete statement and delete first nodes records data and insert second nodes record data.
After run the job, in target table only 25 records (second node)
my solution but not work :-
1) my thinking if it will run (ODBC) on sequential mode or on single node. this idea failed.
Please friend give me suggestion how can delete old records nad insert new records base on CERTIFICATE_TEMPLATE_ID
Requirement -: We are loading data into oracle database based on templated_id so before loading data need to delete recording for same templated_id.
Implement -: in ODBC stage i choose write mode - "delete then insert"
insert statement "INSERT INTO "O2K"."CERT_TEMPLATE_BINDING"("CERTIFICATE_TEMPLATE_ID","TEMPLATE_VARIABLE","FIELD_NAME") VALUES(ORCHESTRATE.CERTIFICATE_TEMPLATE_ID,ORCHESTRATE.TEMPLATE_VARIABLE,ORCHESTRATE.FIELD_NAME)"
delete statement "delete from O2K.CERT_TEMPLATE_BINDING where CERTIFICATE_TEMPLATE_ID=ORCHESTRATE.CERTIFICATE_TEMPLATE_ID"
Status - It's working fine but problem with data.
For example- CERTIFICATE_TEMPLATE_ID=449 i have 40 records in CERT_TEMPLATE_BINDING table so when i will run the job first it will delete all 40 records then insert first node records
(suppose we have total 50 records then it will insert first node data as 25 records) and then again execute delete statement and delete first nodes records data and insert second nodes record data.
After run the job, in target table only 25 records (second node)
my solution but not work :-
1) my thinking if it will run (ODBC) on sequential mode or on single node. this idea failed.
Please friend give me suggestion how can delete old records nad insert new records base on CERTIFICATE_TEMPLATE_ID