SQL code for appending a notebook result into an existing database table

wheersink — Tue, 10 Sep 2024 20:02:30 GMT

I am attempting to append the results from a notebook query results table into an existing databricks database table. By chance would someone share an example of the sql code with me?

Re: SQL code for appending a notebook result into an existing database table

szymon_dybczak — Wed, 11 Sep 2024 08:40:10 GMT

Hi @wheersink ,

So let's say you created following table with some sample values.

%sql CREATE TABLE dev.default.employee ( id INT, name STRING, age INT, department STRING ); INSERT INTO dev.default.employee VALUES (1, 'John Doe', 30, 'Finance'), (2, 'Jane Smith', 35, 'HR'), (3, 'Michael Johnson', 28, 'IT');

If you want to insert to this table additional rows that are result of some query you can use following approach:

# Define the SQL query to select new employees new_employees_sql = """ SELECT 4 AS id, 'Emma Green' AS name, 27 AS age, 'Marketing' AS department UNION ALL SELECT 5, 'Tom Blue', 40, 'Operations' """ # Execute the SQL query and get the result as a DataFrame new_employees_df = spark.sql(new_employees_sql) # Insert the result into the employee table new_employees_df.write.insertInto("dev.default.employee", overwrite=False)

You can also use pure sql to perform this action:

%sql -- Create a query to select employees for the new insert WITH new_employees AS ( SELECT 6 AS id, 'Alice Brown' AS name, 32 AS age, 'Engineering' AS department UNION ALL SELECT 7, 'Robert White', 29, 'Engineering' ) -- Insert the results of the query into the existing employee table INSERT INTO dev.default.employee SELECT * FROM new_employees;

topic SQL code for appending a notebook result into an existing database table in Community Articles

SQL code for appending a notebook result into an existing database table

Re: SQL code for appending a notebook result into an existing database table