SQL INSERT Statements

The INSERT statement is used to add new records (rows) to a table in a database.

Basic Syntax

The most common syntax for the INSERT statement is:

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

Example 1: Inserting into specific columns

Let's say we have a table named Customers with columns CustomerID, FirstName, LastName, and Email. We can insert a new customer like this:

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Alice', 'Smith', 'alice.smith@example.com');

In this example, we assume CustomerID is an auto-incrementing primary key, so we don't need to provide a value for it.

Example 2: Inserting into all columns

If you provide values for all columns in the correct order, you can omit the column list:

Assuming Customers table has columns CustomerID, FirstName, LastName, Email, and City:

INSERT INTO Customers
VALUES (101, 'Bob', 'Johnson', 'bob.j@example.com', 'New York');

Caution: This syntax is less maintainable. If the table structure changes (e.g., a new column is added), your INSERT statements might break.

Inserting Multiple Rows

Most SQL database systems support inserting multiple rows with a single INSERT statement. The syntax generally looks like this:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1a, value2a, value3a, ...),
    (value1b, value2b, value3b, ...),
    (value1c, value2c, value3c, ...);

Example 3: Inserting multiple records

Inserting multiple products into a Products table:

INSERT INTO Products (ProductName, Category, Price)
VALUES
    ('Laptop', 'Electronics', 1200.00),
    ('Desk Chair', 'Furniture', 250.50),
    ('Notebook', 'Stationery', 3.99);

Inserting Data from Another Table

You can also use the INSERT INTO ... SELECT statement to insert data that is already present in another table.

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

Example 4: Copying inactive users to an archive table

Imagine you have a Users table and an InactiveUsersArchive table. You can move users who haven't logged in for a year:

INSERT INTO InactiveUsersArchive (UserID, Username, Email, DeactivationDate)
SELECT UserID, Username, Email, GETDATE() -- Or your database's current date function
FROM Users
WHERE LastLoginDate < DATEADD(year, -1, GETDATE());
Tip: Always ensure data types are compatible between the source and destination columns when inserting data, especially when copying from another table. Use explicit type casting if necessary.

Key Considerations