How to pass a df as table-valued parameter, and once df passed as TPV using that update or insert the output table in SQL Server

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.

https://stackoverflow.com/questions/66962457/how-to-pass-a-df-as-table-valued-parameter-and-once-df-passed-as-tpv-using-that April 06, 2021 at 11:48AM