Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Update: Insert if new SQL Statement Indextype change

chvizda
8 - Asteroid

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'  to
WHERE INDEXTYPE = 'P'
 
somewhere in the Output tool ?
Can I use i.e. the Pre Create SQL statement ?
 
Many thanks for your support
 
Steffen
3 REPLIES 3
BrandonB
Alteryx
Alteryx

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?

randreag
11 - Bolide

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 

 

 

MichaelAd
Alteryx
Alteryx

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

Michael Adler
Sr. Customer Support Engineer -- Knowledge Management Coach
Alteryx, Inc.

Labels