ASA SQL User's Guide
  Adding, Changing, and Deleting Data
    Adding data using INSERT

Inserting values into specific columns

You can add data to some columns in a row by specifying only those columns and their values. Define all other columns not included in the column list must to allow NULL or have defaults. If you skip a column that has a default value, the default appears in that column.

Adding data in only two columns, for example, dept_id and dept_name, requires a statement like this:

INSERT INTO department (dept_id, dept_name)
VALUES ( 703, 'Western Sales' )

The dept_head_id column has no default, but can allow NULL. A NULL is assigned to that column.

The order in which you list the column names must match the order in which you list the values. The following example produces the same results as the previous one:

INSERT INTO department (dept_name, dept_id )
VALUES ('Western Sales', 703)

Values for unspecified columns 

When you specify values for only some of the columns in a row, one of four things can happen to the columns with no values specified:

• NULL entered    NULL appears if the column allows NULL and no default value exists for the column.

• A default value entered    The default value appears if a default exists for the column.

• A unique, sequential value entered    A unique, sequential value appears if the column has the AUTOINCREMENT default or the IDENTITY property.

• INSERT rejected, and an error message appears    An error message appears if the column does not allow NULL and no default exists.

By default, columns allow NULL unless you explicitly state NOT NULL in the column definition when creating tables. You can alter the default using the ALLOW_NULLS_BY_DEFAULT option.

Restricting column data using constraints 

You can create constraints for a column or domain. Constraints govern the kind of data you can or cannot add.

For more information on constraints, see Using table and column constraints.

Explicitly inserting NULL 

You can explicitly insert NULL into a column by typing NULL. Do not enclose this in quotes, or it will be taken as a string.

For example, the following statement explicitly inserts NULL into the dept_head_id column:

INSERT INTO department
VALUES (703, 'Western Sales', NULL )

Using defaults to supply values 

You can define a column so that, even though the column receives no value, a default value automatically appears whenever a row is inserted. You do this by supplying a default for the column.

For more information about defaults, see Using column defaults.