SQL Pocket Guide, 2nd Edition

Inserting Data

Use the INSERT statement to insert new rows into a table. You can insert one row, many rows (DB2 and MySQL), 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, with the default value defaulting to null.

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 into 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 will fail the moment a new column is added to the target table.

Multi-Row Inserts (DB2, MySQL)

DB2 and MySQL 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, ...