Duplicate Entry/Primary Key Error When Using Output Data - Update; Insert if New

[RonGatmaitan]

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.

[atcodedog05]

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 : )

[RonGatmaitan]

@atcodedog05, I think that's the one I'm using already. 🙂

[RonGatmaitan]

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.

[danilang]

Hi @RonGatmaitan 

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

[RonGatmaitan]

Hi, @danilang.

"Is this a case where the primary key in the database includes all the fields in the table?"

No, Sir. I think the problem occurs when the values being update are the same as the values being updated with. In short, the row has the exactly the same values for all columns.

As mentioned, I had to append a datetime field to ensure that a certain value is always moving.

[danilang]

Hi @RonGatmaitan 

We have many workflows that do exactly this.  They work fine when passed records where all the fields are identical to the existing record.  You might want to reach out to Alteryx directly about this, support@alteryx.com. Make sure to include details about the  connection method you're using, ODBC, OLEdb, etc as well what database you're using and the driver details.

Dan 

[Peachyco]

We use Oracle tables primarily, and I usually encounter this when:

• The table does not have a primary/combo key.
• The table was granted a primary/combo key in some fashion that Alteryx does not recognize.

If you are the owner of the destination table, you might try this:

1. Set your Output Data tool to "Overwrite Table (Drop)"
2. Generate the primary/combo key in the Post Create SQL Statement box.
3. If you are successful in running this, reset your Output Data tool to "Update; Insert if New" and clear the Post Create SQL Statement box.

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 NULL
MODIFY "CURRENCY" VARCHAR2 (20) NOT NULL;

ALTER TABLE "EXCHANGE_RATE_SUMMARY"
ADD CONSTRAINT "ExchangeRateSummary" PRIMARY KEY ("PERIOD", "CURRENCY");

[RonGatmaitan]

@danilang, @Peachyco thank you for the reply, guys!