Adding data using INSERT

User's Guide
PART 1. Working with Databases
CHAPTER 8. Adding, Changing, and Deleting Data

Adding data using INSERT

You add rows to the database using the INSERT statement. The INSERT statement has two forms: you can use the VALUES keyword or a SELECT statement:

INSERT using values

The VALUES keyword specifies values for some or all of the columns in a new row. A simplified version of the syntax for the INSERT statement using the VALUES keyword is:

INSERT [ INTO ] table-name [ ( column-name, ... ) ]

VALUES ( expression , ... )

You can omit the list of column names if you provide a value for each column in the table, in the order in which they appear when you execute a query using SELECT *.

INSERT from SELECT

You can use a SELECT statement in an INSERT statement to pull values from one or more tables. A simplified version of the syntax for the insert statement using a select statement is:

INSERT [ INTO ] table-name ( column-name, ... )

select-statement

Inserting values into all columns of a row

The following INSERT statement adds a new row to the department table, giving a value for every column in the row:

INSERT INTO department 
VALUES ( 702, 'Eastern Sales', 902 )

Notes

The values are entered in the same order as the column names in the original CREATE TABLE statement, that is, first the ID number, then the name, then the department head ID.
The values are surrounded by parentheses.
All character data is enclosed in single quotes.
You need to use a separate insert statement for each row you add.

Inserting values into specific columns

You can add data to some columns in a row by specifying only those columns and their values. All other columns that are not included in the column list must be defined to allow NULL or must have defaults. If you skip a column that has a default value, the default is used.

Adding data in only two columns, for example, dept_id and dept_name, requires a statement like this:

INSERT INTO department (dept_id, dept_name)
VALUES ( 703, 'Western Sales' )

The dept_head_id column has no default, but can allow NULL. A NULL is assigned to that column.

The order in which you list the column names must match the order in which you list the values. The following example produces the same results as the previous one:

INSERT INTO department (dept_name, dept_id )
VALUES ('Western Sales', 703)

Values for unspecified columns

When you specify values for only some of the columns in a row, one of four things can happen to the columns with no values specified:

NULL is entered This occurs if the column allows NULL and no default value exists for the column.
A default value is entered This occurs if a default exists for the column.
A unique, sequential value is entered This occurs if the column has the AUTOINCREMENT default or the IDENTITY property.
The INSERT is rejected and an error message is displayed This occurs if the column does not allow NULL and no default exists.

By default, columns allow NULL unless you explicitly state NOT NULL in the column definition when creating tables. You can alter the default using the ALLOW_NULLS_BY_DEFAULT option.

Restricting column data using constraints

You can create constraints for a column or user-defined data type. Constraints govern the kind of data that can or cannot be added.

For information on constraints, see Using table and column constraints.

Explicitly inserting NULL

You can explicitly insert NULL into a column by entering NULL. Do not enclose this in quotes, or it will be taken as a string.

For example, the following statement explicitly inserts NULL into the dept_head_id column:

INSERT INTO department 
VALUES (703, 'Western Sales', NULL )

Using defaults to supply values

You can define a column so that, even though no value is inserted into the column, a default value is automatically filled in whenever a row is inserted. You do this by supplying a default for the column.

For information about defaults, see Using column defaults.

Adding new rows with SELECT

To pull values into a table from one or more other tables, you can use a SELECT clause in the INSERT statement. The select clause can insert values into some or all of the columns in a row.

Inserting values for only some columns can come in handy when you want to take some values from an existing table. Then, you can use update to add the values for the other columns.

Before inserting values for some, but not all, columns in a table, make sure that a default exists or that NULL has been specified for the columns for which you are not inserting values. Otherwise, an error is generated.

When you insert rows from one table into another, the two tables must have compatible structures—that is, the matching columns must be either the same data types or data types between which Adaptive Server automatically converts.

Example

If the columns are in the same order in their create table statements, you do not need to specify column names in either table. Suppose you have a table named newproduct that contains some rows of product information in the same format as in the product table. To add to product all the rows in newproduct:

INSERT product
SELECT * 
FROM newproduct

You can use expressions in a SELECT statement inside an INSERT statement.

Inserting data into some columns

You can use the SELECT statement to add data to some, but not all, columns in a row just as you do with the VALUES clause. Simply specify the columns to which you want to add data in the INSERT clause.

Inserting Data from the Same Table

You can insert data into a table based on other data in the same table. Essentially, this means copying all or part of a row.

For example, you can insert new items into the product table that are based on existing products. The following statement adds new Extra Large Tee Shirts (of Tank Top, V-neck, and Crew Neck varieties) into the product table. The identification number is ten greater than the existing sized shirt:

INSERT INTO product
SELECT id+ 10, name, description, 
    'Extra large', color, 50, unit_price
FROM product
WHERE name = 'Tee Shirt'