SQL - Insert Query

SQL - The INSERT Statement

1. The SQL INSERT INTO Statement

The INSERT INTO statement is used to add new rows of data to a table. It is a fundamental Data Manipulation Language (DML) command.

Syntax

There are two common forms of the INSERT INTO statement.

Form 1: Specify both the column names and the values to be inserted. This is the recommended approach as it’s more robust to changes in table structure.

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

Form 2: If you are adding values for all the columns of the table in their original order, you do not need to specify the column names.

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

Example

First, let’s create a Customers table.

CREATE TABLE Customers (
    id INT PRIMARY KEY AUTO_INCREMENT, -- Use SERIAL for PostgreSQL
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(100),
    signup_date DATE
);

Now, let’s insert a single record using the recommended syntax. We can omit columns that allow NULL or have default values, like city and signup_date.

INSERT INTO Customers (name, age)
VALUES ('Alice', 30);

2. Inserting Multiple Rows at Once

Most modern SQL databases support inserting multiple rows in a single statement, which is more efficient than running multiple individual INSERT statements.

INSERT INTO Customers (name, age, city, signup_date)
VALUES
    ('Bob', 25, 'New York', '2023-01-10'),
    ('Charlie', 35, 'Los Angeles', '2023-02-20'),
    ('Diana', 28, 'Chicago', '2023-03-15');

Verification

You can verify the inserted data with a SELECT query.

SELECT * FROM Customers;

id	name	age	city	signup_date
1	Alice	30	NULL	NULL
2	Bob	25	New York	2023-01-10
3	Charlie	35	Los Angeles	2023-02-20
4	Diana	28	Chicago	2023-03-15

3. Inserting Data from Another Table

A common task is to copy data from one table to another. This is done using an INSERT INTO ... SELECT statement.

Syntax

INSERT INTO table1 (column1, column2, ...)
SELECT columnA, columnB, ...
FROM table2
WHERE condition;

The data types of the source and target columns must be compatible.

Example

Let’s create a Premium_Customers table and copy all customers over 30 years old into it.

CREATE TABLE Premium_Customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

-- Now, copy data from the Customers table
INSERT INTO Premium_Customers (id, name, city)
SELECT id, name, city
FROM Customers
WHERE age > 30;

Note: The archaic INSERT INTO ... TABLE syntax is not standard SQL and should be avoided. INSERT INTO ... SELECT is the correct, portable method.

4. Retrieving Data After Insertion (RETURNING / OUTPUT)

Often, you need to get information about the row you just inserted, like its auto-generated id. Modern databases provide clauses for this, avoiding a second SELECT query.

PostgreSQL and Oracle: RETURNING

INSERT INTO Customers (name, age, city)
VALUES ('Eve', 40, 'Houston')
RETURNING id, name;

This query will insert the new row and immediately return the id and name of that new row.

SQL Server: OUTPUT

INSERT INTO Customers (name, age, city)
OUTPUT INSERTED.id, INSERTED.name
VALUES ('Frank', 50, 'Miami');

5. Practical Considerations: Transactions and Error Handling

When inserting data, especially multiple related rows, it’s crucial to wrap your statements in a transaction. A transaction is an all-or-nothing operation. If any statement within the transaction fails, all previous changes can be undone (ROLLBACK).

BEGIN TRANSACTION; -- Or START TRANSACTION in MySQL

-- Attempt to insert multiple records
INSERT INTO Orders (customer_id, order_date) VALUES (1, '2023-05-01');
INSERT INTO Order_Items (order_id, product_id, quantity) VALUES (101, 1, 2); -- Assume this fails

-- If an error occurred, you would issue a ROLLBACK.
-- If everything succeeded, you finalize the changes with:
COMMIT;

If you try to insert data that violates a constraint (e.g., a PRIMARY KEY or NOT NULL constraint), the database will raise an error, and the INSERT will fail. Using transactions ensures that partial data is not left in your database.