Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

In-DB reading and updating the same table not working

Highlighted
5 - Atom

I am using in-DB functionality of Alteryx. I am reading from a table, perform some mathematical calculations and update back (write back) to the same table.

 

There are two ways i tried

 

1) Delete & Append - In this case the Deletion happens early and no records to perform the calculation and append

2) Append Existing - This throws an error - "Error: Write Data In-DB (2): Error running PreSQL on "NoTable": [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PK__TEST_12__654A5F644830B400'. Cannot insert duplicate key in object 'dbo.TEST_12'. The duplicate key value is (001, 0033, Facebook).[Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated"

 

I want to do this with In-Db functionality. Could some one help

Highlighted
Alteryx
Alteryx

@shobin02 You get that error because one of the fields have a duplicate value that is already inside the table that you are trying to write back to (due to PK). I believe if you were to try this in MS SQL server or even SQL Developer (Oracle client), both RDBMS will give you a similar error. I would filter out that duplicate and try writing out again. 

Digan
Alteryx
Highlighted
8 - Asteroid

@ This one really ground my gears too but I did some small tests with various options and I think I 'may' have solved it to an extent, so am sharing just in case it helps. 

 

I found writing to a temporary table first, just prior to writing back to the source (now the target) table, saw the records reload to the table in their modified/updated form. This behaviour was consistent across both 'delete & append' and 'drop table' modes in the final write. I suspect this is holding the records safely in temp, whilst the table is dropped/truncated and without refreshing what you want to reload, but can't be sure so no warrenties supplied

 

Caveat ... this was a small scale test with a handful of records and an overwrite scenario rather than an incremental update one, but hopefully this might kick off some solutionising by others.

 

alteryx IN DB.PNG

Labels