Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Identity insert during import

 Post Reply Post Reply
Author
Grobety Stephane View Drop Down
Senior Member
Senior Member
Avatar

Joined: 04 Jan 2014
Posts: 121
Post Options Post Options   Quote Grobety Stephane Quote  Post ReplyReply Direct Link To This Post Topic: Identity insert during import
    Posted: 11 Nov 2019 at 4:29pm
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.


Back to Top
Grobety Stephane View Drop Down
Senior Member
Senior Member
Avatar

Joined: 04 Jan 2014
Posts: 121
Post Options Post Options   Quote Grobety Stephane Quote  Post ReplyReply Direct Link To This Post Posted: 11 Nov 2019 at 5:25pm
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?
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2566
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 11 Nov 2019 at 5:39pm
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.
Back to Top
Grobety Stephane View Drop Down
Senior Member
Senior Member
Avatar

Joined: 04 Jan 2014
Posts: 121
Post Options Post Options   Quote Grobety Stephane Quote  Post ReplyReply Direct Link To This Post Posted: 12 Nov 2019 at 10:36am
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.
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2566
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 19 Nov 2019 at 12:56am
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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down