SQL Pocket Guide, 3rd Edition

Inserting Data

Use the INSERT statement to insert new rows in a table. You can insert one row, many rows, or the results of a subquery.

Single-Row Inserts

The following example adds a county to the gov_unit table. The values in the VALUES clause correspond to the columns listed after the table name:

INSERT INTO gov_unit
   (id, parent_id, name, type)
VALUES (13, 3, 'Chippewa', 'County');

Any columns you omit from an INSERT statement take on their default values specified at table-creation time. If you do not specify a default value at table-creation, then a null is used.

Use the DEFAULT keyword to specify explicitly that a column should take on its default value. Use the null keyword to insert a null value explicitly in a column that might otherwise default to a non-null value. For example:

INSERT INTO gov_unit
   (id, parent_id, name, type)
VALUES (14, DEFAULT, 'Mackinac', NULL);

If your VALUES list contains a value for each of the table’s columns in the order specified at table creation, you can omit the column list:

INSERT INTO gov_unit
VALUES (15, DEFAULT, 'Luce', 'County');

For anything other than an ad-hoc insert (in other words, for inserts you embed in your scripts and programs), it’s safer to specify a list of columns. Otherwise, such queries can fail the moment a new column is added to the target table.

Multirow Inserts

Many platforms provide the ability to insert multiple rows via repeated value lists in the VALUES clause:

INSERT INTO gov_unit (id, parent_id, name, type) VALUES (16, 3, ...