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

[RodL]

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

[LindaT]

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

[RemcoD]

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

[RemcoD]

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?

[RodL · Accepted solution]

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.

[J123]

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

[Vlecato]

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.

[riosjosh]

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.

[aelam]

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.