MS SQL Identity Column using In-Database Tools

[RemcoD]

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

[riosjosh]

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]

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.