Alteryx Designer Desktop Discussions

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

MS SQL Identity Column using In-Database Tools

RemcoD
6 - Meteoroid

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

11 REPLIES 11
riosjosh
7 - Meteor

Thanks for the suggestion aelam. The main topic/issue/question at hand however is not "how" to create a Record ID (or similar) using In-DB tools, but rather a workaround for the short coming of the Write-In-DB tool not being able to write data to a table with an identity column (SQL Server itself is the one who populates this field).

 

Apologies, I should've been more specific and pointed in my original reply. The suggestion by @RodL does work to create a RecordID, but it doesn't help if you have a table with an identity column and don't want to change its current setup. Currently, there is no solution for that. I've suggested it as an idea here: Allow for a column list to be specified when using the Write Data In-DB tool

corwinckler
5 - Atom

Ive had to deal with this too. What worked, but was a bit tedious is to create a VIEW with all the columns of the underlying table, EXCEPT the identity column. I then direct the in-db-write to insert into the VIEW.  

Labels