INSERT INTO

The insert into statement is used to insert new rows into a table.

Syntax : INSERT [OR IGNORE | OR REPLACE] INTO TABLE_NAME [ ( Column1, Column2, ColumnN... ) ] VALUES (Value1, Value2, ValueN...) ;

DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, FirstName Text
, LastName Text
, Email Text
);

/* Column names are supplied */
INSERT INTO Users(Username, FirstName, LastName, Email) values ('johnd','John','Dekota', 'johnd@example.com');
INSERT INTO Users(Username, FirstName, LastName, Email) values ('maryd','Mary','Denware', 'maryd@example.com');

/* Column names are supplied skipping Email */
INSERT INTO Users(Username, FirstName, LastName) values ('scotth','Scott','Hamilton');

/* No column names supplied, all values are required. */
INSERT INTO Users values ('gurum','Guru','Manna', 'gurum@example.com');
INSERT INTO Users values ('roberto','Robert','Olipo', ''); /* empty value for email */

SELECT * FROM Users;

INSERT OR IGNORE

Insert or ignore check if primary key already exists and if it does then insert statement is ignored without producing any error. It is similar to ON CONFLICT during table creation.

/* following will produce not unique error due to roberto primary key violation */
INSERT INTO Users values ('roberto','Robert','Olipo', ''); 

/* following will NOT produce not unique error even roberto primary key violation due to OR IGNORE */
INSERT OR IGNORE INTO Users values ('roberto','Robert','Olipo', '');

INSERT OR REPLACE

Insert or replace works by either inserting or updating existing row. It check if primary key already exists and if it does then statement updates all columns with requested insert values.

/* following will update the row due to roberto in primary key. It will act as an update rather than insert */
INSERT OR REPLACE INTO Users values ('roberto','Robert','Olipo', 'roberto@example.com'); 

/* Short Syntax, act same as above */
REPLACE INTO Users values ('roberto','Robert','Olipo', 'roberto@example.com');

SELECT * FROM Users;

/* Either inserts petera or replaces it if it exists */
REPLACE INTO Users values ('petera','Peter','Adam', 'petera@example.com');

Warning! If you do not pass all column values when using REPLACE, the default values for columns are used and can leads to data losses and data integrity issues.

/* following will update the row due to roberto primary key. It will use default values for other columns */
INSERT OR REPLACE INTO Users(Username, Email) values ('roberto', 'roberto@example.com') /* WRONG */; 

/* Notice we lost FirstName and LastName */
SELECT * FROM Users;

Default Values

If default values are assigned during table creation then you can insert default values just by simply using Default Values statement, if there are no default values assigned then default value for each column data type will be used. The primary key or other constraint violations are ignored when default values are used.

DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, FirstName Text DEFAULT 'FName'
, LastName Text  DEFAULT 'LastNameValue'
, Email Text
);

INSERT INTO Users DEFAULT VALUES;
INSERT INTO Users DEFAULT VALUES;

SELECT * FROM Users;

INSERT INTO SELECT

The INSERT statement can contain a SELECT statement instead of a VALUES clause. A new row is inserted into the table for each row returned by SELECT statement. If a columns list is specified, the number of columns in the result of the SELECT must be the same as the number of items in the columns list. If no columns are specified, the number of columns in the result of the SELECT must be the same as the number of columns in the table. Any SELECT statement, including compound statements with ORDER BY .LIMIT clauses, may be used in an INSERT INTO statement when using this form of insert.

DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, FirstName Text
, LastName Text
, Email Text
);

INSERT INTO Users values ('gurum','Guru','Manna', 'gurum@example.com');
INSERT INTO Users values ('roberto','Robert','Olipo', 'roberto@example.com') ;

DROP TABLE IF EXISTS UsersNew;
CREATE TABLE IF NOT EXISTS UsersNew (
Username Text Primary Key
, FirstName Text
, LastName Text
, Email Text
);

INSERT INTO UsersNew SELECT * FROM Users;

SELECT * FROM UsersNew;

Check the github example, how to use parameters when inserting through c#