Update: Insert if new SQL Statement Indextype change

Question

Hi all

I have an Output tool to a Teradata DB and I want to use the Update: Insert if new feature.

Unfortunately the DB Admin of Teradata has created all my tables with a PRIMARY INDEX and not with a PRIMARY KEY. Therefore the Update: Insert if new will not work.

As I check the SQL statement sent by Alteryx I see:

SELECT INDEXTYPE,COLUMNNAME,COLUMNPOSITION, INDEXNAME
FROM DBC.INDICESV
WHERE INDEXTYPE = 'K'
AND DATABASENAME = 'B2B_xxxxxxx'
AND TABLENAME = 'T_VIZDA_xxxxx'Is there a way to change the statement WHERE INDEXTYPE = 'K'  toWHERE INDEXTYPE = 'P' somewhere in the Output tool ?Can I use i.e. the Pre Create SQL statement ?Many thanks for your supportSteffen

madler_alteryx · Answer

Hi @chvizda,

Alteryx requires a primary key for updates. Unless the DBA will agree to add it to the table the update statement will fail.

Thank you,

Michael

randreag · Answer

hi @chvizda

I don't think you can change that.

Are you planing to load a lot of data? I have used the option "insert if new" and for many records it takes too long to update.

What I do usually in these cases is to get the ids I want to update, send them in a delete sentence and then reinsert the updated records

Perhaps this can help you

BrandonB · Answer

Could you write the data to another table and then use PostSQL to execute your command and then drop the initial table used for staging?