SQL Insert: What You Need to Know Today

A characteristic of persistent storage, including relational databases, is the ability to create, read, update, and delete information. This is known as “CRUD”; for databases these SQL actions map to CRUD like this:

This article explores SQL Insert, the ability to insert information into a database.

Syntax of SQL Insert

There are two variants of the SQL Insert statement; the first specifies the columns to be populated and the values to be inserted.

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

If you’re adding values for all the columns in the table the second variant omits the column names. Two caveats:

1. Ensure the order of the values specified in your SQL Insert statement matches the left-to-right structure of the table.
2. Ensure that any proposed changes to the table structure is matched with an audit such that you change your SQL code to populate all columns of the new structure.

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

A Sample Table for SQL Insert

We’ll use a table named Olympic_Winners which looks like this:

Because we’re discussing SQL Insert we start with an empty table.

SQL Insert in Practice

Following are SQL Insert statements to illustrate the valid usage.

(Run these SQL code samples yourself at SQL Fiddle. The SQL to create this sample table is included at the end of this article.)

The Terse Variant

This variant of SQL Insert specifies a value for each column in the table.

INSERT INTO Olympic_Winners
VALUES (
    1908,
    'Tug-of-War',
    'gold',
    'UK',
    'City of London Police',
    'William Hirons' ) ;

After this SQL statement is executed our table looks like:

The Verbose Variant

This variant specifies each column to be used and the value to be inserted. If table columns are added after-the-fact this SQL code will continue to work, inserting NULL values into the new columns (as long as NOT NULL is not specified for the new columns).

One doesn’t need to fully populate a row with values; here’s an example of using the verbose variant of SQL Insert to add only a few values:

INSERT INTO Olympic_Winners
VALUES (
    'Tug-of-War',
    'UK',
    'City of London Police' ) ;

After this SQL statement is executed our table looks like:

SQL Update is used to add values to an already-existing row.

Multi-Row Variant

Specified by SQL-92 and supported by many databases the following syntax causes several rows of values to be inserted with one SQL Insert statement.

INSERT INTO Olympic_Winners (
    SPORT,
    COUNTRY )
VALUES
    ( 'Tug-of-War', 'US' ),
    ( 'Tug-of-War', 'UK' ) ;

Check your database documentation for the exact syntax needed for multi-row SQL Insert; there are several platform-dependent variations.

Conclusion

SQL Insert is at the core of programmatic computation, where data is stored in a relational database. It’s part of the “create, read, update, and delete” (CRUD) information lifecycle and it’s a powerful tool for you to wield.

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

Start Learning

SQL Insert Sample Table Code

The following SQL will build and populate the sample table used in this article.

CREATE TABLE Olympic_Winners (
    YEAR INT,
    SPORT VARCHAR(33),
    MEDAL VARCHAR(33),
    COUNTRY VARCHAR(33),
    TEAM VARCHAR(33),
    MEDALIST VARCHAR(33) ) ;