<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="sequenceobjects.html" title="Sequence Objects" />
<link rel="next" href="updatestmt.html" title="UPDATE Statement" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="insertSTMT" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="sequenceobjects.html" title="Sequence Objects">Prev</a> | <a class="link" href="updatestmt.html" title="UPDATE Statement">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div class="selected">
<a href="insertSTMT.html">INSERT Statement</a>
<div>
<a href="#insertsoft" title="INSERT SOFT">INSERT SOFT</a>
<a href="#insertreplacing" title="INSERT REPLACING">INSERT REPLACING</a>
</div>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="insertSTMT" />
<h2>8.17. INSERT Statement</h2>
<div>
<pre class="programlisting">
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.
</pre>
</div>
<p>
New rows (or records) are entered into a database using the INSERT statement.
</p>
<p>
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.
</p>
<p>
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.
</p>
<p>
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.
</p>
<a name="insertsoft" />
<h3>8.17.1. INSERT SOFT</h3>
<p>
<strong>INSERT SOFT</strong> can be used in place of <strong>INSERT INTO</strong>
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.
</p>
<div>
<pre class="programlisting">
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.
</pre>
</div>
<br />
<a name="insertreplacing" />
<h3>8.17.2. INSERT REPLACING</h3>
<p>
<strong>INSERT REPLACING</strong> can be used in place of <strong>INSERT INTO</strong>
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.
</p>
<div>
<pre class="programlisting">
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.
</pre>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="sequenceobjects.html" title="Sequence Objects">Previous</a>
<br />Sequence Objects</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="updatestmt.html" title="UPDATE Statement">Next</a>
<br />UPDATE Statement</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
776
