(Q) Insert into 
Author Message
 (Q) Insert into

Could anyone please send me an example on the insert into SQL.  I ahve
tried and tried to do this with no luck.

Tony Sousan

Fri, 19 Mar 1999 03:00:00 GMT  
 (Q) Insert into

Adds a new row to a table or a view.

        {table_name | view_name} [(column_list)]
{DEFAULT VALUES | values_list | select_statement}


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.


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


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]...)


Is a required keyword used to introduce the list of values for each column
in the column_list or table.


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.


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.


Is a standard SELECT statement used to retrieve the values to be inserted
from an existing table. For details, see the SELECT statement.


INSERT adds new rows only. To modify column values in existing rows, use
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

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.


INSERT permission defaults to the table owner, who can transfer it to other


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

INSERT titles(title_id, title, type, pub_id, notes, pubdate)
        VALUES ('BU1237', 'Get Going!', 'business', '1389',
                'great', '06/18/86')

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'


This example shows the use (and failure) of the DEFAULT VALUES option for
the INSERT statement.



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')

See Also

CREATE TABLE statement  sp_bindefault system stored procedure
CREATE TRIGGER statement        sp_bindrule system stored procedure
Datatypes       sp_unbindefault system stored procedure
DELETE statement        sp_unbindrule system stored procedure
SELECT statement        UPDATE statement

> Could anyone please send me an example on the insert into SQL.  I ahve
> tried and tried to do this with no luck.

> Thanks
> Tony Sousan

Sat, 20 Mar 1999 03:00:00 GMT  
 [ 2 post ] 

 Relevant Pages 

1. A97 Wizard Qs

2. Newbie Qs: Optimizing Stylesheet with Fields

3. Qs on applying style

4. Qs on applying style (word2000)

5. connect to robohelp qs


7. handling event from control qs

8. 2 fairly simple database Qs

9. Qs: VB4.0/OLE/ODBC

10. DBCombos and some general Qs

11. Qs about OO design

12. Two Qs...


Powered by phpBB® Forum Software