Looking for Starter Kits? Head to the Community Gallery! Now formatted as YXIs for easy installation.

Alteryx Designer Desktop Discussions

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

MS SQL Identity Column using In-Database Tools

6 - Meteoroid



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?



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

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.