This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
@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.
@ 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.