SQLite Bulk Insert Examples

The method of inserting the data into the table of the SQLite database is similar to other database tools. Single record or multiple records can be inserted into the SQLite table. When it is required to work with large amounts of data, it is better to execute an insert statement to insert multiple records at a time into the table. This type of task is called a “bulk insert”. Different examples of inserting the bulk insert tasks are shown in this tutorial.

Prerequisite:

You have to complete the following tasks before practicing the SQLite statements of this tutorial.

A. Run the following command to create an SQLite file named “company.db” that contains the database:

B. Run the following SQL statement to create a table named “products” into the “company.db” database. This table contains five fields and a primary key with the auto-increment attribute:

C. Run the following SQLite commands to format the output and check the structure of the “clients” table in tabular format. The “.header” command displays the heading of the output. The “.mode” command prints the output in tabular format. The “pragma” command displays the structure of the clients table:

The following output appears after executing the commands:

Syntax of the INSERT Statement:

The INSERT query can be executed in multiple ways to add records to the table.  Different syntaxes of INSERT query are shown here. If all fields of the table are mandatory, the field names can be omitted from the INSERT query that is in the first syntax.  The INSERT query can be executed by mentioning the mandatory and particular fields that are shown in the second syntax. Multiple records can be inserted into the table using a single INSERT query that is shown in the third syntax.

1. INSERT INTO <table_name>
   VALUES(value, value2, value3, …);

2. INSERT INTO <table_name> (column1, column2, column3, …)
   VALUES(value1, value2, value3,…);

3. INSERT INTO <table_name> (column1, column2, column3, …)
   VALUES
   (value1, value2, value3, …),
   (value1, value2, value3, …),
   …
   (value1, value2, value3, …);

Different Examples of Bulk Insert

Multiple records can be inserted into a table by executing the INSERT query multiple times or by executing the INSERT query one time and defining multiple records with the VALUES clause which is called “bulk insert”.

Example 1: Insert Multiple Records by Multiple Insert Statements

The simple way to insert multiple records using multiple INSERT queries is shown in this example. Run the following three INSERT queries to insert three records into the “clients” table:

If there is no error in these three INSERT queries, the following output appears:

Run the following SELECT query to check if the records are inserted into the table or not:

According to the output, three records are inserted into the table.

Example 2: Insert Multiple Records Using a Single INSERT Statement

Run the following INSERT query to insert multiple records in the “clients” table by mentioning the fields in a single INSERT query:

Run the following SELECT query to check if the records are inserted into the table or not:

If there is no error in the INSERT query, the following output appears:

Run the following INSERT query to insert multiple records in the “clients” table without mentioning any field in a single INSERT query:

Run the following SELECT query to check if the records are inserted into the table or not:

If there is no error in the INSERT query, the following output appears:

Conclusion

The bulk insert is useful when it is required to work with a large amount of data. Multiple records can be inserted easily in the SQLite table using a single INSERT query. The methods of inserting multiple records into the table using a single INSERT statement multiple times and one time are explained in the examples of this tutorial.