MS SQL Identity Column using In-Database Tools

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

Output

Database Connection

Accepted answers

RodL

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.

All comments

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.

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703