An optional keyword that can be used between INSERT and the target table.

table_name

The name of a table that is to receive the data.

( column_list )

A list of one or more columns to add data to. The column_list argument must be enclosed in parentheses and delimited by commas.

VALUES

Introduces the list of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the table. The values list must be enclosed in parentheses.

DEFAULT

Requires that the default value defined for a column is to be used by Microsoft SQL Server Compact 3.5.

NULL

Indicates that the value is unknown. A value of NULL is different from an empty or zero value.

expression

A constant, a variable, or an expression.

derived_table

Any valid SELECT statement that returns rows of data to be inserted into the table.

To replace data in a table, the DELETE statement must be used to clear existing data before loading new data with INSERT. To modify column values in existing rows, use UPDATE.

If the insert column_list is omitted, an insert column list that identifies all columns of the table in the ascending sequence of their ordinal positions is implicit.

A column in the table can be identified only one time in column_list.

If a column is not in column_list, SQL Server Compact 3.5 must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. SQL Server Compact 3.5 automatically provides a value for the column if the column:

Has an IDENTITY property. The next incremental identity value is used.

Has a default. The default value for the column is used.

Is nullable. A null value is used.

The column list and VALUES list must be used when inserting explicit values into an identity column. If the values in the VALUES list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value.

When DEFAULT is used to specify a column value, the default value for that column is inserted. If a default does not exist for the column and the column permits null values, NULL is inserted. DEFAULT is not valid for an identity column.

Columns created with the uniqueidentifier data type store specially formatted 16-byte binary values. Unlike with identity columns, SQL Server Compact 3.5 automatically generates values for ROWGUID columns with the uniqueidentifier data type. During an insert operation, you can use variables with a data type of uniqueidentifier and string constants in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, 36 characters including hyphens, where x is a hexadecimal digit in the range 0-9 or a-f, for uniqueidentifiercolumns. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid value for a uniqueidentifiervariable or column.

When you insert rows, the following rules apply:

If a value is being loaded into columns with an nchar, nvarchar, or varbinary data type, the padding or truncation of trailing blanks (spaces for nchar and nvarchar, zeros for varbinary) is determined as defined in the following table.

Data type

Default operation

nchar/binary

Pad original value, with trailing blanks for the nchar columns and with trailing zeros for the binary columns, to the length of the column.

nvarchar

Trailing blanks in character values inserted into nvarchar columns are not trimmed. Values are not padded to the length of the column.

varbinary

Trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.

If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data type of the column, the statement fails and SQL Server Compact 3.5 displays an error message.

If INSERT is loading multiple rows with SELECT, any violation of a rule or constraint that occurs from the values being loaded causes the complete statement to be stopped, and no rows are loaded.

To run multiple queries simultaneously, you must include a semicolon and a new line character at the end of each statement. All comments should begin with two hyphens (--).

C. Inserting data with fewer values than columns

In the following example, the Employees table has three columns: EmployeeID, LastName, and FirstName. The follow statement adds Ben Smith to the Employees table without supplying a value for EmployeeID.