SQL Tutorial & Tips
Select
Insert
Update
Delete
Open Cursor
Fetch
Close Cursor
Union
#1 Web Hosting Pick PowWeb Hosting Only *On Sale* $4.88 Per Month
Structured Query Language
INSERT
Syntax:
INSERT [INTO]
{table_name | view_name} [(column_list)]
{DEFAULT VALUES | values_list | select_statement}
INTO
Is an optional keyword.
table_name | view_name =
[[database_name.]owner.]{table_name | view_name}
Specifies the name of the table or view used in the INSERT
statement. If the table or view is not in the current database,
use a fully qualified table_name or view_name
(database_name.owner.object_name).
column_list
Lists one or more columns to which data is to be added.
The columns can be listed in any order, but the incoming data
(whether in a values_clause or a select_statement) must be in the
same order as the columns.The column_list is necessary only when
some, but not all, columns in the table are to receive data.
You can leave out items in the column_list and values_clause as
long as the omitted columns are defined to allow null values or the
columns have an associated default (a default or a DEFAULT
constraint). Enclose the column_list in parentheses. If column_list
is not specified, all columns in the receiving table
(in CREATE TABLE order) are assumed to be included.
DEFAULT VALUES
Inserts the default values for all columns. If the column has the
IDENTITY property or the timestamp datatype, the next appropriate
value will be inserted. If a default for the column does not exist
and the column allows NULLs, NULL will be inserted. If any column
of the table does not have a default or does not allow NULL, an error
will be returned and the INSERT statement rejected.
values_list =
VALUES (DEFAULT | constant_expression
[, DEFAULT | constant_expression]...)
VALUES
Is a required keyword used to introduce the list of values for each
column in the column_list or table.
DEFAULT
Inserts the default value for that column. For a column defined with
the timestamp datatype, the next value will be inserted. If a default
does not exist for the column and the column allows NULLs, NULL will
be inserted.
DEFAULT is not valid for an identity column. Columns with the
IDENTITY property should not be explicitly listed in the column_list
or values_clause; the value for an identity column should not be
explicitly entered. For more information, see the CREATE TABLE
statement.
constant_expression
Contains an expression that will evaluate to a constant. The list of
values must be enclosed in parentheses and must match the explicit or
implicit columns list. Enclose non-numeric constants in quotation marks.
select_statement
Is a standard SELECT statement used to retrieve the values to be
inserted from an existing table. For details, see the SELECT statement.
Remarks:
INSERT adds new rows only. To modify column values in existing rows,
use UPDATE.
When inserting rows, these rules apply:
- Inserting an empty string (' ') into a varchar or text column
inserts a single space. All char columns are right-padded to the
defined length. All trailing spaces are removed from data inserted
into varchar columns, except in strings that contain only spaces.
These strings are truncated to a single space.
- If an INSERT statement violates a constraint, default, or rule, or if
it is the wrong datatype, the statement fails and SQL Server displays
an error message. Constraints are defined with either the CREATE TABLE
or ALTER TABLE statement. Defaults are created with the CREATE DEFAULT
statement and rules are created with the CREATE RULE statement.
- Inserting a null value into a text or image column does not create a
valid text pointer, nor does it preallocate a 2K text page. For details
on inserting text and image data, see the text and image Manipulation
topic.
- An INSERT statement must follow the rules for a batch. For details,
see the Batches topic.
When you specify values for only some of the columns in the column_list,
one of three things can happen to the columns that have no values:
- A default value is entered if the column has a DEFAULT constraint,
if a default is bound to the column, or a default is bound to the
underlying user-defined datatype.
- NULL is entered if the column allows NULLs and no default value
exists for the column.
- An error message is displayed and the row is rejected if the column
is defined as NOT NULL and no default exists.
Permission
INSERT permission defaults to the table owner, who can transfer it
to other users.
Examples:
A. INSERT Specifying All Column Values
This example shows an INSERT into the titles table in the pubs database.
All column values are specified in the values_clause.
INSERT titles
VALUES('BU2222', 'Faster!', 'business', '1389',
NULL, NULL, NULL, NULL, 'ok', '06/17/87')
B. INSERT with Column Titles
This example shows an insert into the titles table in the pubs database.
Only the values for the columns listed in the column_list are shown
in the values_list.
INSERT titles(title_id, title, type, pub_id, notes, pubdate)
VALUES ('BU1237', 'Get Going!', 'business', '1389',
'great', '06/18/86')
Get H&R Block At Home Online Basic
File for FREE with H&R Block At Home Online Free Edition.
H&R Block At Home
(FREE, DELUXE or PREMIUM versions)
C. INSERT with Nested SELECT
This example inserts all rows from the authors table (for authors only
in San Francisco)into the newauthors table.
INSERT INTO newauthors
SELECT *
FROM authors
WHERE city = 'San Francisco'
D. INSERT with DEFAULT VALUES
This example shows the use (and failure) of the DEFAULT VALUES
option for the INSERT statement.
INSERT publishers DEFAULT VALUES
go
Msg 233, Level 16, State 2
The column pub_id in table publishers may not be null.
E. INSERT with the DEFAULT Keyword
This example shows the use of DEFAULT as a placeholder to insert
the DEFAULT value for a column(s).
INSERT employee
VALUES ('KLT91469F', 'Katrina', 'L', 'Thompson', DEFAULT, DEFAULT,
DEFAULT, '01/14/95')
