Is it possible to load data into a table using just SQL on Databricks?

Question

I'm familiar with Python on Databricks to load data into a directory like df.write.format('delta').save('/dir/to/output/')

Is there a SQL-like equivalent of that? I can't find any documentation that mentions it like a select into or just any similar ability to write data onto a directory or table.

Would appreciate any tips!

Answer 1

you should be able to use something like

INSERT INTO table_name PARTITION (id = 1234) SELECT name, address FROM persons WHERE name = "ABC";

this is the sql format you should be using as per link here

Answer 2

There are different ways of achieving it:

• using INSERT INTO with SELECT ... FROM ... as pointed by @NNM or with TABLE ... (docs). Please note that INSERT INTO supports direct load into the directory.

• for some source data formats, like, Parquet, CSV, ... you can use COPY INTO (doc) that provides idempotent data loading, guaranteeing that you won't reprocess already processed files.

• if you need to do complex data loading with updates and maybe deletes, not only with inserts, you can use MERGE INTO (doc)