Using pandas to_sql to append data frame to an existing table in sql server gives IntegrityError

user_6379348 0
Viewed 1492

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.

pandas-to-sql pandas sql-server
1 Answers

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)

user_72205450
Related