Alteryx Designer Discussions

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

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live!

SOLVED

MS SQL Identity Column using In-Database Tools

Highlighted
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

Highlighted
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

Highlighted
Alteryx
Alteryx

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

Highlighted
Alteryx Certified Partner

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

 

Highlighted
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?

 

Highlighted
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.

 

Highlighted
5 - 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?

Highlighted
5 - 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
7 - 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.

Highlighted
7 - 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