I'm getting an error of [DELTA_INSERT_COLUMN_ARITY_MISMATCH] while trying to insert into DataBricks using PYODBC. If I run this query, everything works fine in both DataBricks and Python
‘INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) VALUES(CURRENT_TIMESTAMP, 20250827)”
If I run this query, it works fine in Databricks, but not PYODBC
“INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) SELECT CURRENT_TIMESTAMP, 20250827”
If I run the same query through Python PYODBC, it fails giving a [DELTA_INSERT_COLUMN_ARITY_MISMATCH] target table has 23 column(s) but the inserted data has 2 column(s). (There are actually 23 columns, but I only want to insert 2)
“INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) SELECT CURRENT_TIMESTAMP, 20250827”
Then if I run the same query and my table only has 2 rows, but the FILE_DT column is the first column in the table, and the LOAD_DT_TM is the second column, it does the insert, but puts the CURRENT_TIMESTAMP into the FILE_DT column since it's the first one in the table, and puts the CURRENT_TIMESTAMP into the FILE_DT since it's the second column in the table. When I run the exact same query in databricks, it inserts them into the correct columns.
“INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) SELECT CURRENT_TIMESTAMP, 20250827”
I tried the Inserts listed above, but I expect to be able to limit how many columns I'm inserting, and also that the order be done in the correct manner.
It's likely that you are running into issues during SQL dialect translation. You need to pass UseNativeQuery=1 in your ODBC connection string. See Databricks ODBC docs