Reference Manual
   CHAPTER 9. SQL Statements     

INSERT statement

Function 

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

Syntax 1 

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

Syntax 2 

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

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 sequentially in the order in which the columns were created.

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

Character strings inserted into tables are always stored in the same case as 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.

Standards and compatibility 

• SQL/92     Entry level feature.

• Sybase     Supported by Adaptive Server Enterprise.

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