MS SQL Identity Column using In-Database Tools
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Database Connection
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
We know about this issue and there is a defect to address it in the backlog.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for your reply. I'm looking forward to see this issue being resolved.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Has this issue been resolved? Can we now insert records in a SQL table (using Write In-DB tool) which has an identity column?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.