Inserts one or more rows into a table.

Syntax

---

INSERT INTO <TableName>
[(<ColumnName> [,<ColumnName>])]
VALUES (<Value> [,<Value>])|
<QueryExpression>

<QueryExpression> = query with the same number of
columns as the INSERT statement and columns that
are type-compatible with the INSERT columns

Usage

---

Use this statement to insert a row or rows into a table. If a list of columns to populate is not specified, then the number of values specified in the VALUES clause must match the number of columns in the table. All values specified in the VALUES clause must be type-compatible with the specified columns, or all of the columns in the table if the columns are not specified. If a query expression is used to insert multiple rows into a table, then the SELECT column list of the query expression must contain columns or expressions that are type-compatible with the specified columns, or all of the or all of the columns in the table if the columns are not specified.


Examples

---

-- This INSERT statement inserts a new
-- row into the Orders table

INSERT INTO Orders
(OrderNo, ItemNo, QtyOrdered, UnitPrice)
VALUES (1200, 23478, 10, 30.00)

-- This INSERT statement inserts all of
-- the Orders rows for the year 2006 into
-- the ArchivedOrders table

INSERT INTO ArchivedOrders
SELECT * FROM Orders
WHERE OrderDate BETWEEN DATE '2006-01-01' AND DATE '2006-12-31'

Required Privileges

---

The current user must be granted the INSERT and SELECT privileges on the target table. In addition, the current user must be granted the SELECT privilege on any tables referenced in the FROM clause of a query expression, if one is used. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: