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

[wheersink]

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?  

[szymon_dybczak]

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;