As mentioned on the subject, I'm getting an error when inserting/updating rows. I want the records to insert if new and update if existing. The keys are a combination of 2 fields (code and date).
Help, please.
Hi @RonGatmaitan
Output tool while using db connection has a similar option "Update: inset if new" (insert if new and update if existing) you can try that out.
Hope this helps : )
@atcodedog05, I think that's the one I'm using already. 🙂
I did a workaround by adding another field to the database table. I set the value at current datetime so that the value always gets updated. Seems that the error occurs when all fields are exactly similar. I wonder if there's another way to do without having to add a new field. For future reference.
This is an interesting problem. I've never had any problems with Insert:Update if new. If the record is found based on the primary key fields, the other fields in the record are updated. If not a new row is inserted.
Is this a case where the primary key in the database includes all the fields in the table?
Dan
We use Oracle tables primarily, and I usually encounter this when:
If you are the owner of the destination table, you might try this:
For example, I had to use the code below in the Post Create SQL Statement box when I first created my output Oracle table (after which I set it to Update; Insert if New):
ALTER TABLE "EXCHANGE_RATE_SUMMARY"MODIFY "PERIOD" DATE NOT NULLMODIFY "CURRENCY" VARCHAR2 (20) NOT NULL;ALTER TABLE "EXCHANGE_RATE_SUMMARY"ADD CONSTRAINT "ExchangeRateSummary" PRIMARY KEY ("PERIOD", "CURRENCY");
@danilang, @Peachyco thank you for the reply, guys!
