Primary Key Required for Update Option but Primary Key Exists - SQL Server

[ccagle]

Hi, 

This might be my lack of experience, but I'm getting the following error in Alteryx when trying to update records via the output tool (connecting to SQL server) - 

"Primary Key required for Update option"

The issue is that I have a primary key on the table I'm updating, and I'm sending the data in the same exact format (as the current table structure) back to the table to update (and in fact I initially created the table using Alteryx, and then added the primary key via a sql update statement on the server). 

I'm attaching two screenshots - one of the Alteryx error and the other of the table showing the primary key. Note that I dropped and recreated the primary key several times, and re-ran the workflow several times. Also note that I'm testing with only 2 records. It seems very strange that I get this error when there is a primary key on the table. Can anyone assist with this? 

[jwalder]

It doesn't directly address your issue, but I have found it is more useful to use set based operations for updates/inserts particularly since Alteryx won't insert into a table with an identity column and running a couple hundred thousand update statements is way less efficient than a single update using a join. I get that you only have 2 records, but I tend to follow the same model regardless: write to a temp/staging table, execute an update or merge (upsert) set based operation in post-sql. Doing so would bypass your current issue or reveal why it isn't working (type mismatch, name slightly off, etc.).

[ccagle]

Thanks, I was actually going to go that route, but since the max # of records this workflow would process on any given run is 70 (so 70 updates), it seems like the direct update would be best. I definitely understand what you are saying though. Is this perhaps a bug in Alteryx where it isn't able to access the PK and therefore says it is missing? 

[ccagle · Accepted solution]

Okay so after some internal dialogue in our group, it turns out that the connection type matters - OleDB will cause the primary key error, however using an ODBC connection will work. To me this looks like a bug in Alteryx as it appears Alteryx is throwing an incorrect error code. Anyways thanks for the help - maybe this can be put on the fix list...at least to throw the correct error. 

[Treyson]

hey @ccagle We had this same problem, and there is a workaround. If you put 'Use tablename' in as a Pre-SQL statement it will work. It's silly and needs to be fixed. If you haven't yet, please put in a support ticket. The more outcry, the higher these things get prioritized.

[KarenMJM]

For us the Pre-SQL needed to be 'Use [database]' - thank you for the pointer :-)

[al_sweets]

I found I was getting the "Primary Key required for Update option" error because my schemas were not the same. It was not uploading to SQL Server using my default [dbo] schema and therefore I had to specify this in the Output Tool in Alteryx.

[davidhe]

@jwalder Hi there, realizing this thread is a couple years old, can you give any more specifics on how you executed that? 

You mention leveraging a temp table which i would typically do with in-db, but upserts aren't supported with in-db, at least not out of the box? 

[al_sweets]

@davidhe I suspect he just used a regular Output tool to upload his data to a new table, and then used the Post-SQL option to write code that will join the temp table to his destination table and update fields where necessary.

[Leonari]

I'm getting this error and I tried to use the Pre SQL Statement (Use database/tablename) but it didn't work too. Can you explain how you specified which schema you wanted to use?