How to insert from select query – the ultimate guide

  • by

INSERT INTO with SELECT FROM another table as a value

Usually, you use SELECT FROM statement to query data from a table. But there is many ways we can insert data into a table in SQL. It’s also possible to use the results of SELECT query in combination with INSERT INTO statement, to put the queried data into another table, nested in one single query. In this tutorial you will learn how to do it.

Insert the whole columns from one to another table

It’s really simple to insert one column from one table to another. Here is an example of how to insert a column from one table to another:

INSERT INTO table_1 (columnn_table_1)
SELECT column_table_2
FROM table_2;

What does this mean? The SELECT statement retrieves all data from the column column_table_2. Insert statement is used to put selected data into the column named column_table_1 in table_1.

Of course, you can take more than one column to select and copy the data into the second table using sql insert statement:

INSERT INTO table_1 (column_1, column_2, …)
SELECT column_1_table2, column_2_table2, …
FROM table_2;

How to insert from select with conditions

Sometimes you don’t need to copy all data from columns of some table to another. If you want to filter the rows from the column you want to insert, you can add some conditions before inserting as you do it in a regular SELECT statement:

INSERT INTO table_1 (column_1, column_2, …)
SELECT column_1_table2, column_2_table_2, …
FROM table_2
WHERE table_2.column_1_table2 = {some_value};

(You can use a condition that you need after WHERE)

Example: INSERT all customers from Sweden into the ‘order’ table:

INSERT INTO order (firstname, lastname, country)
SELECT FirstName, LastName, Country
FROM Customer
WHERE Country = 'Sweden'

How to insert from columns and predefined values

Imagine a case where you need to insert data into multiple columns, but only one or more of them need to be selected from another table, and the rest is coming from some dataset:

INSERT INTO table_a (column1a, column2a, column3a, column4a …)
SELECT table_b.column1b, 'some value', table_b.column3b, 'some other value',…
FROM table_b
WHERE table_b.col1 = {VALUE};

Insert top rows

You can choose top n rows from the table_1 and insert into table_2 using a query like this:

INTO table_1 (column_1, column_2, …)
SELECT column_1_t2, column_2_t2, …
FROM table_2
WHERE column_1_t2 = {some_value}
ORDER BY column_1_t2;

dbHarbor: SQLite - SQLite database manager for macOS with great UI.
dbHarbor – SQL Client for macOS

Searching for a good GUI Client to work with SQLite databases on mac? dbHarbor is a native macOS tool with intuitive UI to manage SQLite databases.

Leave a Reply

Your email address will not be published. Required fields are marked *