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 workflow right from within the workflow itself. One such operation is creating a Primary Key for your database table and namely using the Pre-Create SQL and Post- Create SQL options in the Input Data and Output Data tools to do that.
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
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.
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!
Hi All,
I am facing issue of duplicate records are getting inserted into staging table and i have used a insert into column in "Post Create SQL statement"
i am using below query to update
Insert Into [DI].[dbo].[Master_Staging]Select * from [D&I.[dbo].[Master_Staging];
