autoincrement=False has no effect on turning on IDENTITY_INSERT in SQL server 2012

[Former user]

seq is defined in database as Identity(10000,1), and I am importing data from another data source and need to insert the identity column as is.

I set the seq to be autoincrement=False but sqlalchemy won't insert the row. Please see the error message.

tbl = Table('tbl', metadata,
    Column('uuid', String),
    Column('name', String),
    Column('seq', BIGINT, autoincrement=False)
)

tbs.insert().values(
    uuid="xx",
    name="name"
)

Error:

sqlalchemy.exc.IntegrityError: (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert explicit value for identity column in table 'tbl' when IDENTITY_INSERT is set to OFF. (544) (SQLParamData)")

[Mike Bayer]

Hello -

Thanks for this bug report. The bug report as reported here does not contain sufficient information to indicate any bug (incomplete test case), but also it appears that you may be misunderstanding the use of the "autoincrement" flag.

"autoincrement=False" on a Column only indicates the behavior of the CREATE TABLE statement as to whether or not it will generate IDENTITY, and additionally sends an indicator to the engine during an INSERT that "SET IDENTITY INSERT OFF" should be invoked.

Therefore the "seq" column would both be marked as "primary_key=True" and as "autoincrement=True", and a single INSERT that includes "seq" within its keys would signal to the SQL server dialect to call "SET IDENTITY_INSERT OFF" before doing the insert.

Also, the 1.0 series may or may not recognize BIGINT as part of the "autoincrement" category though in 1.1 this should be fixed (dont know what version of SQLA this is either).

[Mike Bayer]

There's not a complete test case here nor most of the details that the bug report instructions indicate; in any case, this is very much a usage question so far up until the point we see a real bug so please continue on the mailing list, so that other people having trouble with autoincrement might find our conversation one day: https://groups.google.com/forum/#!forum/sqlalchemy

[Former user]

Thank you.

"and additionally sends an indicator to the engine during an INSERT that "SET IDENTITY INSERT OFF" should be invoked."

Then this will be a problem. If autoincrement=False, it should invoke SET IDENTITY INSERT ON

This ON means that it temporarily turns off the identity column and permits the insertion of data into identity column explicitly.

[Mike Bayer]

"autoincrement" refers to whether or not the column is marked as IDENTITY. The column needs to be primary_key=True and autoincrement=True for the dialect to know that this column is IDENTITY.

[Former user]

Thanks. What I want is inserting a row with a column which is already defined as IDENTITY field in database schema. Then how am I suppose to turn on the identity insert? Can you please shed light on?

[Mike Bayer]

it's automatic. I'll write better answers to your questions if you use the right forum, because nobody will ever see these answers posted here.