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, ...

Get SQL Pocket Guide, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.