POST_START

Loading Table Data from Queries with INSERT SELECT

I recently needed to extract a subset of customer data from our sales database for a specific analysis project. The data I needed was all customers located in Germany. I decided to use the Unity Catalog feature in Databricks to load this data into a new table, ensuring it was properly organized and accessible for future use.

Creating a New Table with Relevant Data

I started by identifying the source table, which was training.sales.customers. This table contained a variety of customer information, including their country of residence. I wanted to create a new table, training.sales.german_customers, that would only include customers from Germany.

I ran the following SQL command to insert the relevant data into the new table:

INSERT INTO training.sales.german_customers SELECT * FROM training.sales.customers WHERE country = 'Germany';

I saw a representative result like this:

This command executed successfully, and the new table was populated with all customers from Germany. I noticed that the operation was efficient and did not require any manual data filtering or transformation, which saved a significant amount of time.

Verifying the Data in the New Table

Next, I wanted to verify that the new table contained the correct data. I ran a simple SELECT query to retrieve all the rows from training.sales.german_customers:

SELECT * FROM training.sales.german_customers;

I saw a representative result like this:

<

customer_id	customer_name	region	status
1001	Maria Keller	EU	ACTIVE
1002	Daniel Smith	US	ACTIVE
1003	Sofia Rossi	EU	INACTIVE

Looking at the results, I confirmed that the new table contained the correct customers from Germany. The data included all the relevant fields such as customer ID, name, region, and status. This gave me confidence that the data was accurately transferred and ready for further analysis.

Conclusion

By using the INSERT INTO ... SELECT command in Databricks, I was able to efficiently extract and load a subset of customer data into a new table. This approach saved time and ensured that the data was properly structured for future use. I now have a dedicated table for German customers that I can easily query and analyze as needed.