Export (0) Print
Expand All

INSERT

SQL Server 2000

Adds new rows to a table.

Syntax
INSERT [ INTO] 
   table_name  [ ( column_list ) ] 
      { VALUES 
      ( { DEFAULT | NULL | expression } [ ,...n] ) 
       }
Arguments
[INTO]
Is an optional keyword that can be used between INSERT and the target table.
table_name
Is the name of a table that is to receive the data.
( column_list )
Is a list of one or more columns in which to insert data. column_list 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™ 2000 Windows® CE Edition (SQL Server CE).
NULL
Indicates that the value is unknown. A value of NULL is different from an empty or zero value.
expression
Is a constant, a variable, or an expression.
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, then 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 once in column_list.

If a column is not in column_list, SQL Server CE must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. SQL Server CE 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 allows 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 CE automatically generates values for ROWGUID columns with the uniqueidentifier data type. During an insert operation, 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) can be used 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 CE 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 entire statement to be terminated, and no rows are loaded.
Examples

A. Using a simple INSERT statement

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

INSERT INTO Customers VALUES ('TSTCU', 'Testing Site Telephony Co.', 'John Kay', '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 in the Northwind database.

INSERT INTO Customers (CustomerID, CompanyName, Country, Phone) VALUES ('XYZAB', 'Xylophone Alphabet Co.', 'USA', '206-321-8765')

C. Inserting data with fewer values than columns

The following example adds Liz Smith to the Employees table in the Northwind database without supplying a value for EmployeeID.

INSERT Employees (LastName, FirstName) VALUES ('Smith', 'Liz')


Show:
© 2014 Microsoft