![INPUT statement [Interactive SQL]](gif/backward.gif)
ASA SQL Reference
SQL Statements
Use this statement to insert a single row (syntax 1) or a selection of rows from elsewhere in the database (syntax 2) into a table.
INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ]
[ ON EXISTING { ERROR | SKIP | UPDATE } ]VALUES
( expression | DEFAULT, ... )
INSERT [ INTO ] [ owner.]table-name
[ ON EXISTING { ERROR | SKIP | UPDATE } ]
[ WITH AUTO NAME ]
select-statement
WITH AUTO NAME clause WITH AUTO NAME applies only to syntax 2. If you specify WITH AUTO NAME, the names of the items in the SELECT statement determine which column the data belongs in. The SELECT statement items should be either column references or aliased expressions. Destination columns not defined in the SELECT statement will be assigned their default value. This is useful when the number of columns in the destination table is very large.
ON EXISTING clause The ON EXISTING clause of the INSERT statement applies to both syntaxes. It updates existing rows in a table, based on primary key lookup, with new values. This clause can only be used on tables that have a primary key. Attempting to use this clause on tables without primary keys generates a syntax error. You cannot insert values into a proxy table with the ON EXISTING clause.
If you specify the ON EXISTING clause, the server does a primary key lookup for each input row. If the corresponding row does not already exist in the table, it inserts the new row as usual. For rows that already exist in the table, you can choose to silently ignore the input row (SKIP), update the values in the input row (UPDATE), or generate an error message for duplicate key values (ERROR).
By default, if you do not specify ON EXISTING, attempting to insert rows into a table where the row already exist results in a duplicate key value error. This is equivalent to specifying ON EXISTING ERROR.
The INSERT statement is used to add new rows to a database table.
Syntax 1 Insert 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.)
Syntax 2 Carry out 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.
If you specify column names, 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 query specification defining the view is updateable and has only one table in the FROM clause.
An inherently non-updateable view consists of a query expression or query specification containing any of the following:
DISTINCT clause
GROUP BY clause
Aggregate function
A select-list item that is not a base table.
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.
Inserting a significant amount of data using the INSERT statement will also update column statistics.
Performance tipsTo insert many rows into a table, it is more efficient to declare a cursor and insert the rows through the cursor, where possible, than to carry out many separate INSERT statements. Before inserting data, you can specify the percentage of each table page that should be left free for later updates. For more information, see ALTER TABLE statement. |
Must have INSERT permission on the table.
None.
INPUT statement [Interactive SQL]
SQL/92 Entry-level feature. INSERT ... ON EXISTING is a vendor extension.
SQL/99 Core feature. INSERT ... ON EXISTING is a vendor extension.
Sybase Supported by Adaptive Server Enterprise.
Add an Eastern Sales department to the database.
INSERT INTO department ( dept_id, dept_name ) VALUES ( 230, 'Eastern Sales' )
Create the table dept_head and fill it with the names of department heads and their departments.
CREATE TABLE dept_head(
pk int primary key default autoincrement,
dept_name varchar(128),
manager_name varchar (128) );
INSERT
INTO dept_head (manager_name, dept_name)
SELECT emp_fname || ' ' || emp_lname AS manager,
dept_name
FROM employee JOIN department
ON emp_id = dept_head_idCreate the table dept_head and fill it with the names of department heads and their departments using the WITH AUTO NAME syntax.
CREATE TABLE dept_head(
pk int primary key default autoincrement,
dept_name varchar(128),
manager varchar (128) );
INSERT
INTO dept_head WITH AUTO NAME
SELECT emp_fname || ' ' || emp_lname AS manager,
dept_name
FROM employee JOIN department
ON emp_id = dept_head_idCreate the table mytab and populate it using the WITH AUTO NAME syntax.
CREATE TABLE mytab(
pk int primary key default autoincrement,
table_name char(128),
len int );
INSERT into mytab WITH AUTO NAME
SELECT
length(t.table_name) AS len,
t.table_name
FROM SYS.SYSTABLE t
WHERE table_id<=10