deleting rows from a table and then inserting rows

[karrisuresh]

I have a target table say EMP table

I have to load 1000 records from a file into EMP table and say all the 1000 records coming from file have only the Dept no's 1,2,3

When my job runs,
my criteria is to delete all the existing records in the table with dept no 1,2,3
and load the fresh 1000 records into the target EMP table

thanks,
Suresh

[DSguru2B]

Use the OPEN command to pass a delete with the where predicate that contains those ids.

[karrisuresh]

Could anyone elaborate on this

thanks
suresh

[DSguru2B]

OPEN/CLOSE commands get executed only once before or after the sql body, respectively.
Open your enterprise stage, go to the 'properties' tab. Its under 'Options Category'

[samsuf2002]

In your target stage you will option for open command (sql executes before loading the table) and close command (sql executes after loading the table), since you need to delete the those entries with id 1,2,3 before loading the table you can use open command to sql for your criteria.

[karrisuresh]

Hi
I am happy to see the option "OPEN" as mentioned
but with write mode as upsert, I could not see that option

when I am trying to use write mode as "LOAD"
I am getting an error

Could you please suggest me a method only with Upsert write method
(where I will use only insert condition and put 1=2 in update condition)
and the "OPEN "command with write method as "UPSERT"

thanks
suresh

[ray.wurlod]

Can you run two jobs in succession, one with Delete, the other with Insert, using the same keys?