Mastering SQLite INSERT INTO: Add Data

SQLite INSERT INTO: Adding Data to your database

The INSERT INTO statement in SQLite is used to add new rows of data into a table. It supports two variations:

1. Inserting values into all columns.

2. Inserting values into specific columns.

By using the INSERT INTO statement, you can populate tables with data, whether the data comes from manual inputs, user input fields, or other queries.

Syntax:

1. Inserting Into All Columns

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

2. Inserting Into Specific Columns

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

Examples

1. Insert Into All Columns

Code:

-- Create a table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    salary REAL
);

-- Insert a new record into the employees table
INSERT INTO employees VALUES (1, 'Alice', 'HR', 50000.00);

Explanation:

• This inserts a record into all columns of the employees table. The values match the column order.

2. Insert Into Specific Columns

Code:

-- Insert a record by specifying only name and salary
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.

Explanation:

• Only the name and salary fields are populated, while other fields (id and department) remain NULL or take default values.

3. Insert Multiple Rows

Code:

-- Insert multiple records in a single query
INSERT INTO employees (name, department, salary) 
VALUES 
('Charlie', 'IT', 70000.00),
('Diana', 'Finance', 65000.00);

Explanation:

• Multiple rows are inserted in one query, reducing the number of database calls.

4. Insert using a SELECT Statement

Code:

-- Create a table for storing backups
CREATE TABLE employee_backup (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL
);

-- Copy data from employees to employee_backup
INSERT INTO employee_backup (id, name, department, salary)
SELECT id, name, department, salary FROM employees;

Explanation:

• This inserts data into employee_backup by copying records from the employees table.

Practical use Cases

Populate a Table

You can use INSERT INTO to populate your database with initial data or add records dynamically in applications.

Importing Data

Combine INSERT INTO with SELECT to import or migrate data between tables.

Handle Defaults

When certain columns have default values, omit those columns in your INSERT INTO statement to rely on the default settings.

Error Handling and Tips

1. Column Mismatch:

Ensure the number of values matches the number of columns if not specifying column names explicitly.

2. Primary Key Conflicts:

If a record with the same primary key already exists, an error will occur. Use INSERT OR REPLACE to handle such cases:

Code:

INSERT OR REPLACE INTO employees (id, name, department, salary) VALUES (1, 'Alice', 'HR', 55000.00);

3. NULL Values:

When a value isn't provided for a column, SQLite assigns a NULL value unless the column has a NOT NULL constraint or a default value.

Advanced Techniques

Insert with Auto-Increment ID

Code:

-- Insert without specifying the id (auto-increment)
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000.00);

Explanation:

• SQLite automatically assigns a unique id for each record when the id column is defined as INTEGER PRIMARY KEY.

Use of Bind Parameters

In applications, use bind parameters to safely insert data:

Code:

-- Example for Python with SQLite
cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ('Frank', 58000.00));

Practical Guides to SQLite Snippets and Examples.