SQLite | Modifying Data | Adding Data with INSERT

Insert values into every column

INSERT INTO table_name VALUES (value1, value2, ...);

Supply one value per column in table order.

create table user (id integer, name text, old integer, address text);
insert into user values (1, 'devkuma', 25, 'Seoul');
insert into user values (3, 'kimkc', 32, 'Daejeon');
insert into user values (5, 'ariakuma', 27, 'Busan');
insert into user values (6, 'happykuma', 20, 'Gwangju');

Supplying three values for this four-column table fails with table user has 4 columns but 3 values were supplied.

Insert into selected columns

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Omitted columns receive their declared default or NULL when no default exists.

create table user (id integer, name text, address text default 'no value');
insert into user (id, name, address) values (1, 'devkuma', 'Seoul');
insert into user (id, name) values (2, 'Busan');
insert into user (id, address) values (3, 'araikuma');

The second row’s address becomes no value; the third row’s name is NULL. Listed columns need not follow table order, although using table order improves readability.

Insert only default values

INSERT INTO table_name DEFAULT VALUES;

For the preceding table, insert into user default values; stores NULL in id and name, and no value in address.

Insert multiple rows

INSERT INTO table_name VALUES
  (value1, value2, ...),
  (value1, value2, ...);

create table user (id integer, name text, old integer);
insert into user values
  (1, 'devkuma', 25),
  (3, 'kimkc', 32),
  (5, 'ariakuma', 27);

One multi-row statement can reduce repeated database round trips.