INSERT INTO:
This SQL command is used to add a new row to a table or a view. The syntax is
INSERT [INTO] {table_name | view_name} [(column_list)]{DEFAULT VALUES | values_list | select_statement}
where
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.
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.
select_statement:Is a standard SELECT statement used to retrieve the values to be inserted from an existing table.
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.
Examples
1. 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')
2. 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'