Primary Key required for Update option

[jeffv]

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

[j_acon]

Put the tool inside a macro, then use the interface tools to setup an action tool to update the sql.