Adding Rows with INSERT

The INSERT statement adds one or more new rows to a table. In a simplified treatment, INSERT has this form:

INSERT [INTO] table_or_view [(column_list)] data_values

The statement causes the data_values to be inserted as one or more rows into the named table or view. column_list is a comma-separated list of column names that can be used to specify the columns for which data is supplied. If column_list is not specified, all the columns in the table or view receive data.

When a column_list does not name all the columns in a table or view, a value of NULL (or the default value if a default is defined for the column) is inserted into any column not named in the list. All columns not specified in the column list must either allow null values or have a default assigned.

INSERT statements do not specify values for the following types of columns because Microsoft® SQL Server™ generates the values for columns of these types:

• Columns with an IDENTITY property that generates the values for the column.
• Columns that have a default that uses the NEWID function to generate a unique GUID value.
• Computed columns.

  These are virtual columns that were defined as an expression calculated from one or more other columns in the CREATE TABLE statement, such as:

  CREATE TABLE TestTable

    (ColA INT PRIMARY KEY,

     ColB INT NOT NULL,

     ColC AS (ColA + ColB) * 2)

    

The data values supplied must match the column list. The number of data values must be the same as the number of columns, and the data type, precision, and scale of each data value must match those of the corresponding column. There are two ways to specify the data values:

• Use a VALUES clause to specify the data values for one row:

  INSERT INTO MyTable (PriKey, Description)

         VALUES (123, 'A description of part 123.')

    

• Use a SELECT subquery to specify the data values for one or more rows.

  INSERT INTO MyTable  (PriKey, Description)

         SELECT ForeignKey, Description

         FROM SomeView