Updating an identity column
Another option is to create a format file to use with the bulk insert.
I would avoid this option if possible as it adds an external object and is more difficult to maintain.
Perhaps it is trying to allocate a sequence to the character part.
That definition highlights the mistake – the sequence value is separate to the character part and should be a separate column.
Note: The next value is the step added to the current seed; not one more than the max value in the table, or even the step from the last or maximum value.
will return the last identity value inserted in any scope.
This means that if a trigger inserts into a table with an identity then that is the value returned.
The easiest way around this is to create a view on the tabke excluding the identity column and bulk insert into the view.
It is a question that often raises passions of almost religious fervour and a search will find many threads on the subject.