General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database
In this tutorial, you will learn the use of INSERT keyword in SQLite.
If you want to insert data ,you must have table. Here we are going to use employee table to insert data.
To learn more about how to create table use the link. If you do not have table use the below query to create employee table.
Create table employee(id int,name text,age int default 25,salary int )
The above query will create employee table with employee id,name,age and salary. To insert data into table follow the below syntax.
Insert into table_name[(column,…)] values(value,[value,…])
If you want to insert data into employee table
Insert into employee values (2,’raja’,23,20000)
*date and text to be quoted.
Here we are passing four values because employee table has four columns, so we must pass four values. Suppose if we are passing five or greater than four or three or less than four values the data will not be stored in the employee table. So if you have N column in a table you must pass N values in the insert command. At the same time the data will be stored in the following column order id,name,age,salary. Furthermore, the default insert takes create table’s column order.
If you want to store data in particular columns only, you have to mention the column list before the table name in the insert query. Here we are going to insert only id,name,salary . We skipped the employees age field. If you skip the column while inserting it will take default values. Here we have mentioned 25 as default value for age column. If you did not mention the default value, the default value is NULL.
Insert into employee (id,name,salary) values(3,’Lax’,25000)
In the below query we have mentioned the column list with table name. Therefore we must pass four values. However, right now you did not know those values, pass NULL as their value.
Insert into employee values (1,’raja’, null,null)
Generally all major DBMS supports default keyword in insert statement. However, default keyword is not supported here in SQLite.
Here am going to take a copy of employee table.
Create table e as select * from employee;
We can create table using select query. Likewise we can use select query to insert data into some other table.
Insert into table_name select_query;
However, you have to note on thing in the above syntax. The select query’s column list must equal to table’s column or else it will throw an error.
Insert into employee select * from e;
Here all the records in the table e inserted into the table employee. The table e is a copy of employee. So both table have same number of column. So insertion is not a problem here. Suppose if your select query’s column list is fall behind or exceeding the destination table’s column list , will throw an error.
Insert into employee(id,name) select id,name from e;
In the above query we mentioned two columns with table name, so the selected query’s column list must be two only or else the insert query will throw an error.
Inserting date into column
The default date format in sqlite is yyyy-mm-dd Create table with date data type column.
Create table edate(event date);
Use the date() or date(‘now’) to insert current system date to column.
Insert into edate values(date(‘now’))
If you want to insert next day date
Insert into edate values(date(‘now’,’+1 day’))