2

I tried to append my pandas dataframe to an existing data table in sql server like below. All my column names in the data are absolutely identical to the database table. 

df.to_sql(table_name,engine,schema_name,index=False,method='multi',if_exists='append',chunksize=100)

But it failed and I got error like below:

IntegrityError: ('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server]
[SQL Server]Cannot insert explicit value for identity column in table 'table_name' 
when IDENTITY_INSERT is set to OFF. (544) (SQLParamData)")

I have non clue what that means and what I should do to make it work. It looks like the issue is IDENTITY_INSERT is set to OFF?. Appreciate if anyone can help me understand why and what potentially I can do. Thanks.

zesla
  • 11,155
  • 16
  • 82
  • 147

1 Answers1

0

In Layman's terms, the data frame consists of primary key values and this insert is not allowed in the database as the INDENTITY_INSERT is set to OFF. This means that the primary key will be generated by the database itself. Another point is that probably the primary keys are repeating in the dataframe and the database and you cannot add duplicate primary keys in the table.

You have two options: First: Check in the database, which column is your primary key column or identity column, once identified remove that column from your dataframe and then try to save it to the database.

SECOND: Turn on the INDENTITY INSERT SET IDENTITY_INSERT Table1 ON and try again. If your dataframe doesn't consists of unique primary keys, you might still get another error.

If you get error after trying both of the option, kindly update your question with the table schema and the dataframe value using df.head(5)

pratsbhatt
  • 1,498
  • 10
  • 20
  • The question then is, how do you execute `SET IDENTITY_INSERT Table1 ON` through the engine? – Sid Kwakkel Feb 10 '21 at 21:04
  • @SidKwakkel How? Any idea? – Sankalp Tambe Jul 29 '21 at 13:02
  • @SidKwakkel as explained in the documentation https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15 You just enter the command `SET IDENTITY_INSERT Table1 ON` in your sql server. It is just an sql statement similar to `select * from table`. – pratsbhatt Jul 29 '21 at 13:16
  • 1
    Yes. but with pandas? 'to_sql' takes engine whereas I tried to execute the above query but it does not work. – Sankalp Tambe Jul 29 '21 at 13:44