0

As part of my development activity in project I need to update or insert the output table. I am trying to pass a DF as table valued parameter in store procedure. By using a stored procedure, I want to achieve this. Could someone please help me here?

I have DF with 33 columns. It has varchar, nvarchar, int, smallint, float and datetime columns.

For example, I tried as mentioned here - https://www.sqlshack.com/table-valued-parameters-in-sql-server/ created, table, type table and procedure as in the above link. BUT only difference is INSTEAD of INSERTING as declaration I tried to pass from a DF.

Sample DF as below.

data = {'No':[1,2,3],'Name':['A','B','C','D']}

df=pd.DataFrame(data)

Changing DF into list -> tuple as in the link -> Is it possible to pass values to a table type parameter from PYODBC to SQL Server?

rec= df.to_records(index=False)
param=[tuple(rec)]
query="{call Usp_InsertLessonMemOpt (?) }"

**PYODBC connection**

cursor=py_con.cursor()
cursor.execute(query,param)
py_con.commit()
py_con.close()

Error : ('HY003', '[HY003] [Microsoft] [ODBC Driver Manager] Program type out of range (0) (SQLBindParametr)')

If I tried with turbodbc

Runtime error Error ! unsupported type identifier for column parameter_1 NULLABLE UNKNOWN TYPE (precision 0, scale 0))

Using: ODBC Driver 13 for SQL Server and python 3.7.9

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
smilepet
  • 11
  • 1
  • Does this previous SO question help, [Python call sql-server stored procedure with table valued parameter](https://stackoverflow.com/questions/51930062/python-call-sql-server-stored-procedure-with-table-valued-parameter)? – AlwaysLearning Apr 06 '21 at 06:07
  • @AlwaysLearning sorry the above link is not answering my question. I want to pass my df as table valued parameter. Then based on the input value either update or insert the records in the output table. using merge option in store procedure. – smilepet Apr 06 '21 at 09:23
  • maybe not the cleanest approach, but what i ended up doing is writing the dataframe to a new table (overwrite mode) in a staging schema. then the stored proc just merges the staged data into the target table – Didier Caron Nov 16 '22 at 13:27

0 Answers0