Share via

  • Article

Adding Rows by Using INSERT and SELECT

You can use the INSERT and SELECT statements to add rows to a table in the following ways:

  • Use the INSERT statement to specify values directly or from a subquery.

  • Use the SELECT statement with the INTO clause.

Using INSERT

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

INSERT [INTO] table_or_view [(column_list)] data_values

The INSERT statement inserts data_values as one or more rows into the specified table or view. column_list is a list of column names, separated by commas, 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 column_list does not specify all the columns in a table or view, either the default value, if a default is defined for the column, or NULL is inserted into any column that is not specified in the list. All columns that are not specified in the column list must either allow for null values or have a default value assigned.

INSERT statements do not specify values for the following types of columns because the SQL Server Database Engine generates the values for these columns:

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

    Computed columns are virtual columns that are 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 following example shows how to insert rows into a table that has columns that automatically generate a value or have a default value. The INSERT statements insert rows that contain values for some of the columns but not all. In the last INSERT statement, no columns are specified and only the default values are inserted.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 timestamp,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

Using INSERT with the VALUE Clause and a SELECT Subquery

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. You can specify the data values in the following ways:

  • By using a VALUES clause to specify the data values for one row. For example:

    INSERT INTO MyTable (PriKey, Description)
       VALUES (123, 'A description of part 123.')

    For more information, see Inserting Rows by Using INSERT and Values.

  • By using a SELECT subquery to specify the data values for one or more rows, such as:

    INSERT INTO MyTable  (PriKey, Description)
       SELECT ForeignKey, Description
       FROM SomeView

    For more information, see Inserting Rows by Using INSERT and SELECT Subqueries

Using SELECT with INTO

To create a new table from values in another table, you can use SELECT INTO. For example:

SELECT LastName, FirstName, Phone
INTO dbo.PhoneList492
FROM Person.Contact
WHERE Phone LIKE '492%'

For more information, see Inserting Rows by Using SELECT INTO.