SQL - INSERT Statement

CIW Course in a Nutshell

SQL, INSERT Statement

The INSERT statement is used to add new records to a database table.

INSERT MyTable (firstname, lastname)
VALUES ('Fred', 'Quimby')

INSERT INTO mytable (firstname, lastname)
VALUES ('Fred', 'Astaire')

There are two syntax styles: INSERT and INSERT INTO. I don't know if there is a difference between them as they appear to be interchangeable in both Microsoft SQL and MySQL. The Microsoft documentation describes INTO as an optional argument.

The general syntax for the statement is:

INSERT Tablename (Column-List)
VALUES (Value-List)

The INSERT keyword is followed by the table name to be used and then a comma-delimited list of column names, in parentheses. This is followed by the VALUES keyword and a further comma-delimited list, this time of the corresponding values to be inserted into the specified columns. gain, this list is in parentheses. The parentheses are NOT optional.

Null Value Fields

If the table contains columns that permit NULL values, these may be omitted from the column-list and the new row will have these fields left with the null values. Every other field, that does not permit null values, must be included in the column-list and provided with valid values.

A field may also be omitted from the column-list if it is an auto number field. This is a field/column that contains and integer value which is incremented each time a row is added. It is often used to provide the table with a unique key. This may be referred to as an autonumber, an autoincrement, or an IDENTITY column.

INSERT INTO MyTable (firstname, lastname)
SELECT Forename, Surname FROM AnotherTable WHERE Surname LIKE 'Smith'

It is also possible to supply the new values from a SELECT statement that queries a second table. In this case the INSERT INTO statement will add as many new rows as are returned by the query.

The above example queries 'AnotherTable' for all names where the Surname is 'Smith', this is used by the INSERT statement to add these records INTO MyTable. The column names in each table are different, but this does not matter as it is only the field values that we are passing.

This also demonstrates SQL's ability to nest SELECT statements inside other statements. A feature that makes SQL very powerful once you get to grips with the concept.

It is probably not a good to try and SELECT records from the same table you are going to INSERT them INTO.