Identity Columns Issue

[tjamal1]

Hi 

I am trying  to insert some data in one table which has identity column but getting this error

An explicit value for the identity column in table ‘dbo.Dim_City’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

I have tried one answer suggested in this post using pre and post SQL statement but not worked for me

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/identity-insert-don-t-tell-me-this-isn...

I am using SQL as source and destination

Is there anyway to resolve this issue ? 

[DiganP]

@tjamal1 This error happens when you are trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and you are assigning a value to the identity column instead of letting SQL Server assign the value. You can try to use the custom mapping inside of the output data tool. 

Here's an article that talks about this error. 

[folopes]

This not work for me. 

I have an ETL process that I need to insert the results into a SQL Server table with Identity column. 

I would like to use In-Db because I need to put this workflow in Alteryx Server, but when I try to append the results into a table I got this error:

An explicit value for the identity column in table 'table_test' can only be specified when a column list is used and IDENTITY_INSERT is ON

Is there a way to do this using In-Db tools? I cannot find the solution. 😞

[ericlaug]

I saw some other threads on this topic. Some suggest that the good people of Alteryx are working on a solution. Until then, I developed a workaround but it involves a few steps. I'll describe this where my destination table is widgets and my identity column is named widgets_id. This is a very high level description of the steps I took.

1. On the SQL server, I created a table nearly identical to widgets named widgets_temp. It did not contain the identity column and it had no primary key.

2. I set up my workflow so that at the start I ran a trivial SQL query that contributed no data to the data stream but did execute some post-SQL that ran a delete query on widgets_temp. I implemented some block-until-done tools to make sure that this task was complete before proceeding downstream.

3. At the end of workflow, I appended the new data to widgets_temp with a write data in-DB tool followed by another block-until-done.

4. I ran another trivial SQL query that also had some post-SQL. This executed a stored procedure that moved the data from widgets_temp to widgets. This let the SQL server manage the identify column rather than having Alteryx encounter the error.

Hope this helps someone out there.

[arunstha10]

Hi,

I came across this problem and used the Record ID tool. To resolve this problem I added a Record ID tool to the workflow and mapped it to the column that has the identity constraint. 

-Arun