• Article

Inserting Rows by Using INSERT and Values

The VALUES keyword specifies the values for one or more rows of a table. The values are specified as a comma-separated list of scalar expressions whose data type, precision, and scale must be the same as or implicitly convertible to the corresponding column in the column list. If a column list is not specified, the values must be specified in the same sequence as the columns in the table or view.

For example, the following statement inserts a single row into the UnitMeasure table by using the VALUES clause.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
GO

The maximum number of rows that can be inserted in a single INSERT statement is 1000. The following example creates the table dbo.Departments and then inserts five rows into the table. Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments 
    VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'), 
           (3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Brewer');

To insert data by using INSERT

INSERT (Transact-SQL)