community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

MS SQL Identity Column using In-Database Tools

Alteryx Certified Partner

Hello,

 

Using a regular Ouput Data Tool, it is possible to insert records into a MS SQL database without specifying the identity column. MS SQL generates the integer id's. Using In-Database Tools it doesn't seem to be possible to insert records into a MS SQL database table which contains an Identity column. The following error occurs:

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

 

Is there any workaround using only In-Database Tools?

 

Remco

Alteryx Alumni (Retired)

I've tried a couple of ideas, but can't figure out a workaround that works. 

Including one of the Product Managers on this to bring to their attention... @JCR

Alteryx
Alteryx

We know about this issue and there is a defect to address it in the backlog.

Alteryx Certified Partner

Thank you for your reply. I'm looking forward to see this issue being resolved.

 

Alteryx Certified Partner

Since it is not possible to use an identity column, is there any way using the In-DB tools to generate row numbers (like the regular tool Record ID) and write these ID's into a regular column?

 

Alteryx Alumni (Retired)

Depending on the database platform, you could try the Row_Number SQL function. 

I have tested this with MS SQL Server and it works when you put this in a Formula In-DB tool...

 

Row_Number() OVER(ORDER BY FieldName ASC)

 

...where "FieldName" is the name of a field in your data.

 

Atom

Has this issue been resolved? Can we now insert records in a SQL table (using Write In-DB tool) which has an identity column?

Atom

It has almost been 2 years since this was placed on the backlog.  Has there been any progress resolving this issue.  I am currently dealing with it now.

Highlighted
Meteor

Also wondering if this has been fixed or it there is a work around now... the solution suggested by @RodL did not work for me. I get the same error.

Meteor

You may try the Sample IN-DB tool.  It doesn't create the Record ID column but identifies a sample based of your column selected.  Example in my workflow I was needing to identify the most recent 113 weeks.  I used 113 for my number in the sample tool.  the results brought back the most recent 113 and removed all other weeks. 

Labels