www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

SQL Reference

Datatypes
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
INSERT SOFT INSERT REPLACING
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
Anytime Queries
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices
Transitivity in SQL
Fast Phrase Match Processor

8.17. INSERT Statement

    insert_statement
	    : INSERT insert_mode table priv_opt_column_commalist values_or_query_spec
	    ;

    insert_mode
	    : INTO
	    | REPLACING
	    | SOFT

    priv_opt_column_commalist
	    : /* empty */
	    | '(' column_commalist ')'

	    ;

    values_or_query_spec
	    : VALUES '(' insert_atom_commalist ')'
	    | query_spec
	    ;

    insert_atom_commalist
	    : insert_atom
	    | insert_atom_commalist ',' insert_atom
	    ;

    insert_atom
	    : scalar_exp
	    ;

    column_commalist
	    : column
	    | column_commalist ',' column
	    ;

    query_spec
	    : SELECT opt_all_distinct selection table_exp
	    ; ... See the SELECT statement next.

New rows (or records) are entered into a database using the INSERT statement.

If you have to enter a NULL you can simply use the keyword NULL, as you would a normal value. Since NULL is a special keyword you do not need to enclose it in single quotes.

You can specify the columns that you are inserting values into in the insert statement. One should always specify the columns that you are inserting into, in case the order of columns in the database are not as expected, or you are not inserting values into every column.

If a value is not specified for a column on insert, then the default value will be used for that column. If no default value has been specified either by a CREATE or MODIFY TABLE statement then NULL will be used.

8.17.1. INSERT SOFT

INSERT SOFT can be used in place of INSERT INTO if you are unsure whether the value to be inserted into a primary key column will violate that constraint. If the row with this primary key already exists, then the new row is not inserted.

SQL> create table insert_test(id integer primary key, txt varchar);
Done. -- 90 msec.
SQL> insert into insert_test(id, txt) values(1, 'test');
Done. -- 0 msec.
SQL> insert into insert_test(id, txt) values(1, 'test');

*** Error 23000: [Virtuoso ODBC Driver][Virtuoso Server]SR197:
  Non unique primary key on DB.DBA.insert_test. at line 4 (4) of Top-Level:
  insert into insert_test(id, txt) values(1, 'test')

SQL> insert soft insert_test(id, txt) values(1, 'testsoft');

Done. -- 0 msec.
SQL> select * from insert_test;
id                txt
INTEGER NOT NULL  VARCHAR
_______________________________________________________________________________

1                 test

1 Rows. -- 60 msec.

8.17.2. INSERT REPLACING

INSERT REPLACING can be used in place of INSERT INTO if you are unsure whether the value to be inserted into a primary key column will violate that constraint. If the row with this primary key already exists, then the new row will be inserted replacing the old values.

SQL> create table insert_test(id integer primary key, txt varchar);
Done. -- 90 msec.
SQL> insert into insert_test(id, txt) values(1, 'test');
Done. -- 0 msec.
SQL> insert into insert_test(id, txt) values(1, 'test');

*** Error 23000: [Virtuoso ODBC Driver][Virtuoso Server]SR197:
  Non unique primary key on DB.DBA.insert_test. at line 4 (4) of Top-Level:
  insert into insert_test(id, txt) values(1, 'test')

SQL> insert replacing insert_test(id, txt) values(1, 'testreplacing');

Done. -- 0 msec.
SQL> select * from insert_test;
id                txt
INTEGER NOT NULL  VARCHAR
_______________________________________________________________________________

1                 testreplacing

1 Rows. -- 0 msec.