All Things SQL

All Things Related To SQL, Databases & Cloud Based Solutions

All Things SQL 

SQL INSERT – Using SELECT Statement

, , , ,

We can write standard insert queries using hard-coded values. Similarly, we can write inserts and instead use a SELECT query to source data from other tables in our database. In order to write a SQL insert using a SELECT statement, we can look at the standard syntax below.

INSERT INTO <schema_name>.<table_name> (column_1, column_2, column_n…)
SELECT value_1, value_2, value_n… FROM some_table; — SQL Insert syntax using SELECT statement

The SQL query itself does not need to be as simple as the above. As a result, our insert query can contain several joins, formulas, calculations, and aggregations. It just needs to be a valid running SQL select query.

Example of Inserting Data with a SELECT Statement

We can see in the example below how a query can have multiple joins, filters, and aggregations while still being valid for a SQL insert.

sql insert into table using select query
Example – Insert Into Table Using SELECT Query

We should always first verify that our SELECT statement is valid and will execute in the database. We must ensure that the query returns with a valid dataset and expected result since this is the data that will be inserted into our new table.

sql invoice data result aggregation
Sample dataset result from SELECT query
Additional Notes & Cautions

A few things to be aware of as we are using an insert with a select query:

  • The data-types should align with source and targets otherwise the database may return an error.
  • The SELECT query will copy all of our source data into our new table unless a filter is specified.
  • Target table must already exist and does not get created. This is not the same as using SELECT INTO.

Even though some of the above may seem obvious, these are important behaviors to note as they may not be obvious to early learners. There are various ways to insert data into tables and this is just one of many.