Inserting Data Into a Table

Introduction

A database table is only as useful as the data it contains. The process of inserting data into tables is one of the most fundamental tasks in SQL. In this guide, you’ll learn how to:

• Insert data into tables using SQL INSERT statements.
• Insert multiple rows efficiently.
• Handle potential errors when inserting data.

Setting Up the Example Table

To practice inserting data, let’s first create an example table called friends. This table will store information about friends, including their ID, first name, and last name.

Table Specifications

SQL to Create the Table

CREATE TABLE friends (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL
);
      

Inserting Data into the Table

Use the INSERT statement to add rows to the friends table. The general syntax is:

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

Example: Adding a Single Row

Let’s insert the first friend into the table:

INSERT INTO friends (id, first_name, last_name)
VALUES (1, 'Amy', 'Pond');
      

Notes:

• String values are enclosed in single quotation marks.
• If the AUTOINCREMENT constraint is applied to a column, you can omit its value, and SQLite will generate it automatically.

Example: Adding Multiple Rows

For efficiency, you can insert multiple rows in a single statement:

INSERT INTO friends (first_name, last_name)
VALUES
  ('Rose', 'Tyler'),
  ('Martha', 'Jones'),
  ('Donna', 'Noble'),
  ('River', 'Song');
      

Advantages of multiple-row inserts:

• Reduces the number of INSERT statements needed.
• If one row fails, all rows in the batch will fail, preserving data integrity.

Verifying the Data

Use the SELECT statement to view the data in the table:

SELECT * FROM friends;
      

The output should look like this:

Handling INSERT Errors

When inserting data, certain constraints may cause errors. For example:

Error: Duplicate Primary Key

Attempting to insert a row with a duplicate id will result in an error because the id column has a PRIMARY KEY constraint:

INSERT INTO friends (id, first_name, last_name)
VALUES (5, 'Jenny', 'Who');
      

SQLite will return:

Error: UNIQUE constraint failed: friends.id

What You Learned

• How to use INSERT statements to add rows to a table.
• How to insert multiple rows at once for efficiency and data integrity.
• Common errors encountered when inserting data, such as duplicate primary keys.

Mastering the INSERT statement is essential for populating and managing SQL tables. Practice frequently to become confident in working with real-world data.