0

Below is my sample databricks code where ACTUAL_TABLE and TEST_TABLE are SQL tables.

I am trying to insert into ACTUAL_TABLE from TEST_TABLE in databricks using pyodc connection.

mssql_db.sql is my connection and the connection is working as expected. Code executes without any error but not inserting any data into the table. Looks like sp_executesql not working from databricks.

V_SQL='INSERT INTO dbo.ACTUAL_TABLE(NAME,GENDER) SELECT ZNAME,GENDER from dbo.TEST_TABLE'
mssql_db.sql(f"""begin declare @V_SQL NVARCHAR(MAX); 
begin
set @V_SQL=N'{V_SQL}'
EXECUTE sp_executesql @V_SQL;
end
end""")

Is there any other way to fulfil this requirement. I wanted to insert it from databricks only.

I also tried creating the proc in SQL and calling that proc from databricks even that didn't work.

Dale K
  • 25,246
  • 15
  • 42
  • 71
pythonCoder
  • 25
  • 1
  • 5

1 Answers1

0

Follow the below approach, I reproduce the same thing in my environment as per your requirement.

This is my demo table ACTUAL_TABLE1.

enter image description here

Now, you can check Azure Databricks connected to the SQL server

Code:

import pyodbc
server = 'dem123.database.windows.net'
database = 'pool123'
username = 'xxxx'
password = 'xxxx'
    
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE='+ database +';UID=' + username + ';PWD='+ password)

cursor = conn.cursor()
sql = 'INSERT INTO dbo.ACTUAL_TABLE1 (NAME, GENDER) SELECT ZNAME, GENDER FROM dbo.TEST_TABLE'

cursor.execute(sql)

conn.commit()
conn.close()

enter image description here

Successfully executed and inserted the data into ACTUAL_TABLE1 using above code:

Output:

enter image description here

B. B. Naga Sai Vamsi
  • 2,386
  • 2
  • 3
  • 11
  • 1
    Thanks @SaiVamsi as I said there is no issue with my connection and other tables are working fine. There was some audit enabled for this actual_table thats why its not getting inserted. I tried disabling the audit and I can see the data now in my ACTUAL_TABLE1 – pythonCoder Mar 03 '23 at 13:59