SQLite INSERT INTO SELECT

SQLite is a lightweight and server-less relational database management system. It requires very minimal configuration which has proven to be very helpful for developers to integrate it into any applications with ease. Due to its server-less architecture, we can use SQLite in various mobile applications as well as in small desktop applications. In this article, we are going to explore the “INSERT INTO SELECT” statement in SQLite. We are going to cover all its use cases with clear and concise examples along with their respective explanations.

INSERT INTO SELECT statement

In SQLite, an INSERT INTO SELECT statement is used to copy one or more tables data into another table. We can insert or copy complete data from one table to another as a whole or with some modifications. Simply it is a statement that allows us to perform data retrieval and data insertion tasks in a single statement.

Syntax:

INSERT INTO table_1 (column_1, column_2,…………………)

SELECT (column_1, column_2,…………………)

FROM table_2;

Examples of INSERT INTO SELECT Statements in SQLite

We already have two tables present in our database. The tables are courses and new_course_info. The data of these two mentioned tables are shown below

Table: courses

Table – courses

Table : new_course_info

Table – new_course_info

We need to create one more table in our database in order to perform operations on that table.

Query:

CREATE TABLE geeksforgeeks 
(
    id INTEGER,
    name TEXT,
    courses INTEGER,
    total_score INTEGER
);

As we have create a new table “geeksforgeeks”, Now we are good to go.

Example 1: Inserting Complete Table’s Data Into Another Table

In this example, we are going to copy one table data completely to another table. For this example, we are going to copy the courses table data completely to our geeksforgeeks table. We are using only table to copy our data without any filtering.

Query:

INSERT INTO geeksforgeeks(id, name, courses, total_score)
SELECT *
FROM courses;

Output:

copying data from single table

Explanation : We can clearly observe that the table above is completely identical to courses table. This is because we have copied the complete data of courses table into our geeksforgeeks table without any filtering.

Example 2: Inserting Data From Multiple Table

In this example, we will combine data of courses and new_course_info tables and insert it into our table geeksforgeeks. Unlike in the previous example, we will use our two tables to fecth data and insert it into our source table.

Query:

INSERT INTO geeksforgeeks(id, name, courses, total_score)
SELECT c.user_id, c.user_name, n. courses, c.total_score
FROM courses c, new_course_info n
where n.id = c.user_id;

Output:

Copying data from multiple tables

Explanation: We can clearly observe that above table has all the fields similar to our courses table except courses column. For this column, we have taken data from from our new_course_info table. Therefore the above image has all the field data similar to courses table except one.

Example 3: INSERT INTO SELECT Statement With WHERE Clause.

In this example, we will use INSERT INTO SELECT Statement with WHERE Clause. We will insert one table data into another table with filtering. Unlike in other examples we have seen above, are not copy the complete data from one table to another table, but we will filter them and then insert them into our table. For this example, we will copy all the data from the courses table to the geeksforgeeks table where user id is less than or equal to 4.

Query:

INSERT INTO geeksforgeeks(id,name,courses,total_score)
SELECT *
FROM courses where user_id <= 4;

Output:

Insert into select statement with where clause

Explanation : We can clearly observe that the above output displayed has all the data copied from courses table, but the last row has been not added. This is because last row has id = 5 , that exceeds our minimum id criteria. Therefore, the last row has not been added.

Conclusion

Overall, INSERT INTO SELECT statement is used to copy data from one or more table to another table with or without filtering. We can completely copy one table data to another or add some modifications to it. We have covered it various examples such as inserting complete table’s data into another table, inserting data from multiple table and INSERT INTO SELECT Statement with WHERE Clause. Now you have a good understanding on how to write INSERT INTO SELECT statement with different types of scenarios. Now you can easily perform queries related to it and can get the desired output.