User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To insert a single row (format 1) or a selection of rows from elsewhere in the database (format 2) into a table.

Syntax

     Format 1

     INSERT INTO [ owner.]table-name [( column-name, ... )]
          ... VALUES ( expression | DEFAULT, ... )

     Format 2

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

Usage

Anywhere.

Permissions

Must have INSERT permission on the table.

Side effects

None.

See also

• INPUT statement
• UPDATE statement
• DELETE statement
• PUT statement

Description

The INSERT statement is used to add new rows to a database table.

Format 1 allows the insertion of a single row with the specified expression values. The keyword DEFAULT can be used to cause the default value for the column to be inserted. If the optional list of column names is given, the values are inserted one for one into the specified columns. If the list of column names is not specified, the values are inserted into the table columns in the order they were created (the same order as retrieved with SELECT *). The row is inserted into the table at an arbitrary position. (In relational databases, tables are not ordered.)

Format 2 allows the user to do mass insertion into a table with the results of a fully general SELECT statement. Insertions are done in an arbitrary order unless the SELECT statement contains an ORDER BY clause. The columns from the select list are matched ordinally with the columns specified in the column list or the columns in the order they were created.

Note The NUMBER(*) function is useful for generating primary keys with format 2 of the INSERT statement (see "Watcom-SQL Functions").

Inserts can be done into views provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a GROUP BY clause, an aggregate function, or involve a UNION operation.

Character strings inserted into tables are always stored in the case they are entered, regardless of whether the database is case sensitive or not. Thus a string Value inserted into a table is always held in the database with an upper-case V and the remainder of the letters lower case. SELECT statements return the string as Value . If the database is not case-sensitive, however, all comparisons make Value the same as value , VALUE , and so on. Further, if a single-column primary key already contains an entry Value , an INSERT of value is rejected, as it would make the primary key not unique.

Performance hint To insert many rows into a table, it is more efficient to declare a cursor and use the PUT statement to insert the rows, where possible, than to carry out many separate INSERT statements.

Examples

Add an Eastern Sales department to the database.

     INSERT
     INTO department ( dept_id, dept_name )
     VALUES ( 230, 'Eastern Sales' )

Fill the table dept_head with the names of department heads and their departments.

     INSERT
     INTO dept_head (name, dept)
     SELECT emp_fname || ' ' || emp_fname
                 AS name,
             dept_name
     FROM employee JOIN department
     ON emp_id = dept_head_id