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.
SOLVED

Primary Key required for Update option

jeffv
8 - Asteroid

I'm getting the above error and somewhat understand it but not sure how to get around it.

 

Some background.  I pull in some data from excel file that contain a list of transactions.  I have an inventory table in SQL server where the primary key is defined  to be serialnr.  The data coming from excel in has serialnr and are a list of new items or items needing to be updated in the inventory.  After transforming data to be in correct format and excluding some rows with problems from excel input, I come up with  set of rows with multiple columns that need to be added to sql server inventory if the record doesn't exist and updated if serialnr does exist.

 

I use an odbc connection to sqlserver with the output options "update: insert if new".  When workflow run it errors with above error.  I use a custom field map as a couple of fields have different names in excel vs sql server.(guess I could rename them with "select"

 

It kind of makes sense why I get the error.  How does alteryx know how to issue query to update and/or insert.  I assume all it has is the fact known in sql server has a primary key of serialnr.   Does it issue several queries? (how can I see those?)  Does it try to append and if it fails with error of  failure on primary key then tries to do an update of those rows fields (except serianr because it's the key) to the table?  Not sure it is getting the queries right and thus the error.

 

I wondered if both tables (I read other community with made me think so) needed primary key... but so far as I know one can't put a primary key on data from excel.

 

So I'm kind of stuck not knowing how to proceed. 

 

I can manually separate rows that should update table and those needing to be inserted.  Inserting seems straight forward, but I'm still not sure how I issue a update command directly in alteryx.  (I see it's been suggested by users).

 

Any help appreciated.

 

Thanks,

Jeff

 

 

10 REPLIES 10
Inactive User
Not applicable

It is most likely failing due to the field schemas between what is in Alteryx vs what is in the SQL table. Update Insert will work if the schemas are the same. It checks the primary key, if the key exists in Alteryx it will update the record in SQL, if not it will insert. Use a select tool and deselect the new/different fields and make sure the fields that exist in SQL are named appropriately in Alteryx with the correct data types.

j_acon
9 - Comet

I have gotten the update to work, i just had to have the same field names. I

 

In my case, the Table in the Database had PK_VIP as the primary key, in my file the primary was named PKVIP, so all i did was rename it to PK_VIP and it worked from there. I use Update: Warn on Failure.

 

 

jeffv
8 - Asteroid

Appreciate the replies!  I kept working with it and quite honestly don't know whether it was the same fields or the PK but eventually split it into two events and update and an insert.  After the replies and playing with it I though to close out the issue I'd go back to one to test again and it seems to work.

 

Again thanks for giving me ideas to work with!

 

Jeff

mcbridewilliam
6 - Meteoroid

I'm running into a similar problem when trying to write to a Hadoop table.  I don't have a unique key in my data and I'm wondering how to get around this requirement?    Basically, how can I trick Alteryx to think it has a primary key?

pereisyd
7 - Meteor

I'm still facing that error, even though my columns are exactly the same. Does the Alteryx version have anything to do with it? I'm using 2018.4.4

j_acon
9 - Comet

Is the data type the same as well? and is the primary define in the DB?

Srikanth1729
5 - Atom

Make sure your table name matches exactly- including the case of character have to match for the update to work seamlessly.

 

HW1
9 - Comet

Update option inserts rows in your database table when:

  1. You have designated a primary key column in your table in your database.
  2. That key is a unique value i.e. one value is not repeated in any row of the primary key column.
  3. The dataframe schema (from alteryx) is exactly the same as the table in your database. That means the file names, file types and sizes.

 

Hence, I would check the database table first to examine the columns and their types and then match them exactly using the select tool in Alteryx prior to output.

 

Please note, if you have selected the update tables option and there are more than one rows with the same primary key, that row values will get updated i.e. replaced with newer values. The database will not allow two rows with the same primary key. Hence, it would be advisable to understand what your workflow and/or data is and then create a primary key column that would be unique.

franc1s
8 - Asteroid

@HW1, @jeffv@pereisyd et al, I believe I can validate 1 and 2, but 3 is almost impossible. I was unable to make it to work. It does not help that the error message is too generic to give any real clues about what the problem is.

 

I came up with a alternative solution that is similar, but not the same. Instead of a insert or update in place, I am executing a "PRE CREATE SQL" statement prior to an "Append existing". This statement throws away data for which an update is available. This does not require that the table has a primary key (although not having it would make the deletion slower).

 

Capture_2202.PNG

 

 

 

 

This is less efficient that an "Update, insert if new", but, at least it works.

 

One question that I now have is, how can I make the parameter to the PRE-CREATE SQL dynamic? I mean, I do not wont to have to update the workflow every time to update '202202XX' to something else. I would want to read it in from a text file with parameters for the workflow.

 

 

I will still do a bit of digging into the error code that is thrown up. If I find anything new, I will share it.

 

 

 

I will still do a little bit more digging into the reason(s) behind the error, and share if I find something new.

 

 

 

 

Labels