SQLite – INSERT INTO with SELECT FROM table as a value

  • by

Usually, you use SELECT FROM statement to query data from a table. But it’s also possible to you 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.

1. 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 table_1 to table_2:

INSERT INTO table_1 (columnn_table_1)
SELECT column_table_2
FROM table_2;

What does this mean? Using this SQL statement you select all data from column_table_2 and insert into column_table_1 in table_1.

Of course, you can take more than one column to select and insert into the second table:

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

2. INSERT conditional SELECT

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'

3. Insert only top rows

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

INSERT TOP(n)
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.

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 *