Identity insert during import

BIZ TMS Aurelius
1

Hello,


In an application that is targetted at MSSQL server (2012+), I need to insert row identity columns with my data (it's an import from a legacy DB and that ID needs to be preserved during import).

I have defined my entity as such:

  [Entity]
  [Table('t_users')]
  [Model(STR_MODEL_SYSTEM)]
  [UniqueKey('username')]
  [UniqueKey('UserGUID')]
  [UniqueKey('userID')]
  [Id('Frecno', TIdGenerator.IdentityOrSequence)]
  TBTUser = class
  private
    [Column('recno', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    Frecno: Int64;

During the import phase, I must be able to insert the identity. That is not much of an issue with MSSQL: all I have to do is call "SET IDENTITY_INSERT  t_users ON"  before perforing the insert.
However, I don't know how I can define the aurelius entity so that it retains its "Required", "NoInsert" and "NoUpdate" properties and still be able to define it during import.


2

Actually, even when removing NoInsert and NoUpdate and using a nullable<int64> as column type, I get an error: I can't seem to run the "SET IDENTITY_INSERT  t_users ON" statement on the same context as the object manager's "flush" command (or, at the very least, I get an identity insert error).


Any suggestion on how I could get around this?
3

Which identity error you get?

When you define an ID of type IdentityOrSequence, Save will fail complaining that your entity already has an ID. In this case, use Replicate instead of Save. Note that when using Replicate, if the record already exists in the database with the ID, it will be update with new data.
4
here is what I'm getting:

exception class    : EMSSQLNativeException
exception message  : [FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot update identity column 'recno'.

Replicate, however, seems to work.
5

That's the purpose of Replicate.

The message you are getting is expected as it's trying to update an identity column, which is not allowed for SQL Server. SET IDENTITY_INSERT only applies to INSERT records, not UPDATE.