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

Output Data Tool - update, insert if new

dla
6 - Meteoroid

Hi Alteryx Community

 

I have read this post on creating a database table and setting a Primary Key:

FadiB wrote:

One of the powerful things about the Alteryx Designer is that you can do most things related to your...

 

So far so good.

 

I then change the Output Data Tool to "Update; Insert if new". I get an error, which is also mentioned in the post: "Primary Key required for Update option…

Make sure a primary key is declared on the table."

 

But I have already added a Primary Key (verified this in SQL Management Server) - so what is missing?

 

Do I need to add some Pre- or Post-Create SQL statement to execute the Update and Insert actions? And if so, how should this be done? 

- Or should I be using some of the In Database tools instead of the standard Output Data Tool?

 

I sincerely hope that one of you in here can help me out. 

 

Kind regards,

Dorte

5 REPLIES 5
Inactive User
Not applicable

You should not require adding a post/pre sql statement. If you went into SQL Server Management Studio>Design> and then set primary key it should be fine. Make sure that you saved the table after you set the primary key and are able to set the key (user restriction). Update Insert should work just fine then. Can you send a screenshot of the table with the key set? Best way to validate this is to check the table again under design and you should see the key already visible.

 

If you have a post or pre sql statement that adds the key I would think you should remove it as you won't require that after the first run; try that as well if you already have keys set.

dla
6 - Meteoroid

Hi Ryan 

 

Thanks for replying to my post, confirming that it should work this way. I am not sure, what I have done wrong before, but ...

 

I have made it work now!!

 

I create the new table with the post-create sql, remove it afterwards and change to Update;Insert if new. Then run my workflow again, and it updates and inserts as it should!

 

I even made it work with a composite primary key as well:

 

ALTER TABLE [Movia].[dbo].[dataleveringbus]
ALTER COLUMN "respid" int NOT NULL;

ALTER TABLE [Movia].[dbo].[dataleveringbus]
ALTER COLUMN "Respondentnummer" varchar(11) NOT NULL;

ALTER TABLE [Movia].[dbo].[dataleveringbus]
ALTER COLUMN "Målepunkt" varchar(250) NOT NULL;

ALTER TABLE [Movia].[dbo].[dataleveringbus]
ADD CONSTRAINT PK_ID_BUS PRIMARY KEY (respid, Respondentnummer, Målepunkt);

 

Thanks again. This is an awesome Community!

deepubcbsnc
5 - Atom

To add on, the "Update: Insert if new" works well with Unique Primary Key on the table. If you have only a primary index, it fails with above error. But when it comes to handling million records, the work flow becomes slow.

 

Please let me know if you faced the issue and is there any solution for this ? 

KasiaP
7 - Meteor

Hi,

 

I have a table with primary key set this way:

ALTER TABLE [dbo].[JustTest] ADD CONSTRAINT ID_Unique Primary key (ID)

 

and I am trying to update it with output tool, insert if new option. I get an error 'Primary Key required for update option'

Does anyone know why? How should I set up primary key/uniquness to make it work?

 

hali11251
6 - Meteoroid

HI

How many columns do you have in this table. when you do upsert,  Alteryx expecting more than one columns, as primary key is not be updated in upsert. If you don't have nay other column beside primary key in the custom mapping then you will get this error.

 

Labels