Delete and Insert Oracle OCI-Stage
Moderators: chulett, rschirm, roy
Delete and Insert Oracle OCI-Stage
Hi there,
i'm extracting data with the SAP BW Extract-Pack. One attribute is YEAR.
I like to write all the extracted data into an oracle table.
Problem is: i like to delete all the existing data of YEAR from the oracle table and then write all the new data into the oracle table.
e.g: extract pack comes with YEAR = 2005
in the oracle table, there are 500 data records with YEAR=2004 and 1000 data records with YEAR=2005 - so the job should delete all the 1000 data records with YEAR = 2005 and then write all the new records.
I tried it with the before-stage: delete from #table# where year:=40 --> problem: not all variables bound
any ideas
thx in advance
matthias
i'm extracting data with the SAP BW Extract-Pack. One attribute is YEAR.
I like to write all the extracted data into an oracle table.
Problem is: i like to delete all the existing data of YEAR from the oracle table and then write all the new data into the oracle table.
e.g: extract pack comes with YEAR = 2005
in the oracle table, there are 500 data records with YEAR=2004 and 1000 data records with YEAR=2005 - so the job should delete all the 1000 data records with YEAR = 2005 and then write all the new records.
I tried it with the before-stage: delete from #table# where year:=40 --> problem: not all variables bound
any ideas
thx in advance
matthias
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
As an academic exercise, you can also do this with two links, ordered correctly.
Have your first output link from the transformer send only the YEAR column to an OCI stage. Constrain it so it only sends one row - the first row. Set the update action to 'delete' based on that single key field and set the transaction size to 1 so the deletes are immediately commited.
Your second link can do the inserts are you are doing them now.
Have your first output link from the transformer send only the YEAR column to an OCI stage. Constrain it so it only sends one row - the first row. Set the update action to 'delete' based on that single key field and set the transaction size to 1 so the deletes are immediately commited.
Your second link can do the inserts are you are doing them now.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers