SQLite | Inserting, Updating, and Deleting Data | Inserting Data Selected from Another Table

Tags:

In addition to inserting explicitly specified values, an INSERT statement can add rows returned by a SELECT statement from another table. This article explains how.

Inserting Data Selected from Another Table

Use the following syntax.

INSERT INTO table_name SQL_statement;

The destination table must have the same number of columns as the query result, although the column names may differ. For example:

INSERT INTO table1 SELECT C1, C2, C3 FROM table2 WHERE condition;

In this example, table1 must define three columns.

You can also specify the destination columns.

INSERT INTO table_name (column1, column2, ...) SQL_statement;

The number of specified destination columns must still match the number of columns returned by the query.

INSERT INTO table1 (N1, N2, N3) SELECT C1, C2, C3 FROM table2 WHERE condition;

Create the source table and insert data into it.

create table user (id integer, name text, old integer);

insert into user values (1, 'devkuma', 24);
insert into user values (2, 'kimkc', 31);
insert into user values (3, 'araikuma', 18);
insert into user values (4, 'happykuma', 25);
insert into user values (5, 'mykuma', 19);

sqlite> create table user (id integer, name text, old integer);
sqlite> 
sqlite> insert into user values (1, 'devkuma', 24);
sqlite> insert into user values (2, 'kimkc', 31);
sqlite> insert into user values (3, 'araikuma', 18);
sqlite> insert into user values (4, 'happykuma', 25);
sqlite> insert into user values (5, 'mykuma', 19);
sqlite>

Create the destination table.

create table olduser (id integer, name text, address text);

sqlite> create table olduser (id integer, name text, address text);
sqlite>

Select rows from user whose old value is greater than 20, then insert their id and name values into olduser.

insert into olduser (id, name) select id, name from user where old> 20;

sqlite> insert into olduser (id, name) select id, name from user where old> 20;
sqlite>

Query olduser to verify the inserted rows. The example first queries user for comparison.

select * from olduser;

sqlite> select * from user;
id          name        old       
----------  ----------  ----------
1           devkuma     24        
2           kimkc       31        
3           araikuma    18        
4           happykuma   25        
5           mykuma      19        
sqlite> 
sqlite> select * from olduser;
id          name        address   
----------  ----------  ----------
1           devkuma               
2           kimkc                 
4           happykuma      
sqlite>

Only source rows whose old value is greater than 20 were inserted. Because the statement did not specify a value for the address column in olduser, SQLite stored its default value, NULL.