sql_insert

SQL INSERT INTO Statement

The INSERT INTO statement is used to add new rows (records) of data into a table. This is how you populate your tables after defining their structure with CREATE TABLE.

The SQL INSERT INTO Statement

You can use the INSERT INTO statement in a couple of ways, depending on whether you are providing values for all columns or only for specific ones.

SQL INSERT INTO Syntax

Form 1: Inserting values for all columns

If you are providing values for all columns in the table, in the order they were defined in CREATE TABLE, you don’t need to specify the column names.

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

Caution: This form is less robust. If the table structure changes (e.g., columns are added or reordered), this statement might fail or insert data incorrectly. It’s generally recommended to use Form 2.

Form 2: Specifying column names and values (Recommended)

This form explicitly lists the columns you are inserting data into, followed by the corresponding values. This is safer and more readable.

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

The order of value1, value2, ... must match the order of column1, column2, ....

Inserting Multiple Rows

Most modern SQL databases allow inserting multiple rows in a single INSERT INTO statement, which is more efficient than multiple separate INSERT statements:

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

Demo Database Schema

Let’s assume we have a Customers table defined as follows:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT, -- Or IDENTITY(1,1) for SQL Server, or SERIAL for PostgreSQL
    CustomerName VARCHAR(255) NOT NULL,
    ContactName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(100),
    PostalCode VARCHAR(20),
    Country VARCHAR(100)
);

Note: AUTO_INCREMENT (MySQL), IDENTITY (SQL Server), or using a sequence with SERIAL (PostgreSQL) means the CustomerID will be automatically generated by the database when a new row is inserted, ensuring uniqueness.

INSERT INTO Example: Inserting a Full Record

Let’s insert a new customer into the Customers table, providing values for all columns except CustomerID (which is auto-generated).

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('New Wave Imports', 'Helen Bennett', '200 Market St', 'San Francisco', '94107', 'USA');

After this statement, the Customers table will have a new row. The CustomerID for this new customer will be automatically assigned by the database.

For example, if this was the first record, it might look like:

CustomerID | CustomerName      | ContactName   | Address        | City            | PostalCode | Country
-----------|-------------------|---------------|----------------|-----------------|------------|--------
1          | New Wave Imports  | Helen Bennett | 200 Market St  | San Francisco   | 94107      | USA

Insert Data Only in Specified Columns

It’s also possible to insert data only into specific columns. Any columns not listed in the INSERT INTO statement will receive their DEFAULT value (if defined) or NULL (if the column is nullable and has no default). If a column is NOT NULL and has no DEFAULT value, and you omit it, the INSERT will fail.

The following SQL statement will insert a new row, but only provide data for CustomerName, City, and Country.

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Urban Living', 'London', 'UK');

The Customers table might then contain (assuming this is the second record):

CustomerID | CustomerName      | ContactName   | Address        | City            | PostalCode | Country
-----------|-------------------|---------------|----------------|-----------------|------------|--------
1          | New Wave Imports  | Helen Bennett | 200 Market St  | San Francisco   | 94107      | USA
2          | Urban Living      | NULL          | NULL           | London          | NULL       | UK

Here, ContactName, Address, and PostalCode are NULL for the ‘Urban Living’ customer because they were not specified and are nullable columns without default values.

Practical Application: Adding a new user to a registration system, recording a new sale in an e-commerce platform, logging an event in an application log.

Best Practice: Always use the explicit column list (Form 2) for INSERT statements to make your SQL more maintainable and less prone to errors if the table schema changes.

Retrieving Inserted ID: Many applications need to know the ID of the newly inserted row (especially for auto-generated primary keys). Database systems offer ways to retrieve this, e.g., LAST_INSERT_ID() (MySQL), SCOPE_IDENTITY() (SQL Server), or the RETURNING clause (PostgreSQL, SQLite).