INSERT (SQL Server Compact)

Adds new rows to a table.

Syntax

INSERT [INTO] 
      table_name  [ ( column_list ) ] 
      { VALUES 
      ( { DEFAULT | NULL | expression } [ ,...n] ) 
| derived_table
       }

Arguments

  • [INTO]
    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.

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

Remarks

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 must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. SQL Server Compact 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 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 uniqueidentifier columns. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid value for a uniqueidentifier variable 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 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.

Note

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 (--).

Code Examples

A. Using a simple INSERT statement

The following example adds a new company to the Customers table. Where certain information is unavailable, a null value is inserted.

INSERT INTO Customers VALUES ('TPC', 'The Phone Company', 'John Kane', 'Owner', NULL, 'Forks', NULL, NULL, 'USA', NULL, DEFAULT);

B. Inserting data that is not in the same order as the columns

The following example uses column_list and the VALUES list to specify explicitly the values that are inserted into each column in the Customers table.

INSERT INTO Customers ([Customer ID], [Company Name], Country, Phone)
VALUES ('WWI', 'Wide World Importers', 'USA', '206-555-0165');

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.

INSERT INTO Employees ([Last Name], [First Name]) VALUES ('Smith', 'Ben');

D. Inserting data using a derived table

The following example uses a derived_table to insert values.

INSERT INTO Table1 (col1, col2) SELECT (col1, col2) from Table2;

E. Inserting date values

The following example shows how to insert a date value.

INSERT INTO Orders ([Order ID], [Customer ID], [Order Date])
VALUES (34, 'FRANS', '4/2/2004');