Insert into SQL table using jdbc connection from databricks

Question

I wanted to insert data into sql table from databricks. I have already set-up jdbc sql connection in databricks. I am using below query but its giving me parse exception

%sql

query=insert into table company(name,location) values('abc','xyz')
df=spark.write.jdbc(url=url,table=query,properties=properties)

display(df)

Note-url and properties are already set-up during SQL JDBC connection in databricks

Vamsi Bitra · Accepted Answer · 2022-10-13 07:56:38Z

1

Please follow the below code:

Create a data frame with sample values and insert the values into the SQL server with connection properties.

Code

Server_name = "<server_name>.database.windows.net"
Database = "<database>"
Port = "1433"
user_name = "<your_user_name>"
Password = "<Your_password>"
jdbc_Url = "jdbc:sqlserver://{0}:{1};database={2}".format(Server_name, Port,Database)
conProp = {
  "user" : user_name,
  "password" : Password,
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

#Write
df=spark.createDataFrame([(1, "gopal","sol"),(2,"sam","chennai")],["PersonID", "LastName","City"])
df.write.jdbc(url=jdbc_Url,table="Persons",mode="overwrite",properties=conProp)

enter image description here

Output:

enter image description here

For more information refer this MS Document.

answered Oct 13, 2022 at 7:56

Vamsi Bitra

2,7642 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codetech Over a year ago

Thank you your code is working but I have to change mode from overwrite to append. mode overwrite is throwing error "cannot drop table"

codetech Over a year ago

Suppose If wanted to perform update or delete statement on existing table then what mode we would use?

Collectives™ on Stack Overflow

Insert into SQL table using jdbc connection from databricks

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related