Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Adding, Changing, and Deleting Data » Adding data using INSERT

Inserting values into all columns of a row Next Page

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 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, DepartmentID and DepartmentName, requires a statement like this:

INSERT INTO Departments ( DepartmentID, DepartmentName )
VALUES ( 703, 'Western Sales' );

Execute a ROLLBACK statement to undo the insert.

The DepartmentHeadID 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 Departments ( DepartmentName, DepartmentID )
VALUES ( 'Western Sales', 703 );

Execute a ROLLBACK statement to undo the insert.

Inserted values for specified and unspecified columns

Values are inserted in a row according to what is specified in the INSERT statement. If no value is specified for a column, the inserted value depends on column settings such as whether to allow NULLs, whether to use a DEFAULT, and so on. In some cases, the insert operation may fail and return an error. The following table shows the possible outcomes depending on the value being inserted (if any) and the column settings:

Value being insertedNullableNot nullableNullable, with DEFAULTNot nullable, with DEFAULTNot nullable, with DEFAULT AUTOINCREMENT
<none>NULLSQL errorDEFAULT valueDEFAULT value DEFAULT value
NULLNULLSQL errorNULLSQL error DEFAULT value
specified valuespecified valuespecified valuespecified valuespecified valuespecified value

By default, columns allow NULL values unless you explicitly state NOT NULL in the column definition when creating a table. You can alter this default using the allow_nulls_by_default option. You can also alter whether a specific column allows NULLs using the ALTER TABLE statement. See allow_nulls_by_default option [compatibility] and ALTER TABLE statement.

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 entering 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 DepartmentHeadID column:

INSERT INTO Departments
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.