<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Query Language: INSERT</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
</script>
</div>
<div class=nosearch><h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>INSERT</h2></div><p><b><a href="syntax/insert-stmt.html">insert-stmt:</a></b>
<button id='x1107' onclick='hideorshow("x1107","x1108")'>hide</button></p>
 <div id='x1108' class='imgcontainer'>
 <img alt="syntax diagram insert-stmt" src="images/syntax/insert-stmt.gif" />
<p><b><a href="syntax/expr.html">expr:</a></b>
<button id='x1109' onclick='hideorshow("x1109","x1110")'>show</button></p>
 <div id='x1110' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x1111' onclick='hideorshow("x1111","x1112")'>show</button></p>
 <div id='x1112' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif" />
</div>
<p><b><a href="syntax/raise-function.html">raise-function:</a></b>
<button id='x1113' onclick='hideorshow("x1113","x1114")'>show</button></p>
 <div id='x1114' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif" />
</div>
<p><b><a href="syntax/type-name.html">type-name:</a></b>
<button id='x1115' onclick='hideorshow("x1115","x1116")'>show</button></p>
 <div id='x1116' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram type-name" src="images/syntax/type-name.gif" />
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x1117' onclick='hideorshow("x1117","x1118")'>show</button></p>
 <div id='x1118' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/select-stmt.html">select-stmt:</a></b>
<button id='x1119' onclick='hideorshow("x1119","x1120")'>show</button></p>
 <div id='x1120' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram select-stmt" src="images/syntax/select-stmt.gif" />
<p><b><a href="syntax/common-table-expression.html">common-table-expression:</a></b>
<button id='x1121' onclick='hideorshow("x1121","x1122")'>show</button></p>
 <div id='x1122' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram common-table-expression" src="images/syntax/common-table-expression.gif" />
</div>
<p><b><a href="syntax/compound-operator.html">compound-operator:</a></b>
<button id='x1123' onclick='hideorshow("x1123","x1124")'>show</button></p>
 <div id='x1124' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram compound-operator" src="images/syntax/compound-operator.gif" />
</div>
<p><b><a href="syntax/join-clause.html">join-clause:</a></b>
<button id='x1125' onclick='hideorshow("x1125","x1126")'>show</button></p>
 <div id='x1126' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-clause" src="images/syntax/join-clause.gif" />
<p><b><a href="syntax/join-constraint.html">join-constraint:</a></b>
<button id='x1127' onclick='hideorshow("x1127","x1128")'>show</button></p>
 <div id='x1128' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-constraint" src="images/syntax/join-constraint.gif" />
</div>
<p><b><a href="syntax/join-operator.html">join-operator:</a></b>
<button id='x1129' onclick='hideorshow("x1129","x1130")'>show</button></p>
 <div id='x1130' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram join-operator" src="images/syntax/join-operator.gif" />
</div>
</div>
<p><b><a href="syntax/ordering-term.html">ordering-term:</a></b>
<button id='x1131' onclick='hideorshow("x1131","x1132")'>show</button></p>
 <div id='x1132' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram ordering-term" src="images/syntax/ordering-term.gif" />
</div>
<p><b><a href="syntax/result-column.html">result-column:</a></b>
<button id='x1133' onclick='hideorshow("x1133","x1134")'>show</button></p>
 <div id='x1134' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram result-column" src="images/syntax/result-column.gif" />
</div>
<p><b><a href="syntax/table-or-subquery.html">table-or-subquery:</a></b>
<button id='x1135' onclick='hideorshow("x1135","x1136")'>show</button></p>
 <div id='x1136' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram table-or-subquery" src="images/syntax/table-or-subquery.gif" />
</div>
</div>
<p><b><a href="syntax/with-clause.html">with-clause:</a></b>
<button id='x1137' onclick='hideorshow("x1137","x1138")'>show</button></p>
 <div id='x1138' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram with-clause" src="images/syntax/with-clause.gif" />
<p><b><a href="syntax/cte-table-name.html">cte-table-name:</a></b>
<button id='x1139' onclick='hideorshow("x1139","x1140")'>show</button></p>
 <div id='x1140' style='display:none;' class='imgcontainer'>
 <img alt="syntax diagram cte-table-name" src="images/syntax/cte-table-name.gif" />
</div>
</div>
</div>


<p>The INSERT statement comes in three basic forms.  
<ol>
<li><p><b>INSERT INTO </b><i>table</i><b> VALUES(...);</b>
<p>The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. If the <span class='yyterm'>column-name</span> list after
<span class='yyterm'>table-name</span> is omitted then the number
of values inserted into each row
must be the same as the number of columns in the table. In this case
the result of evaluating the left-most expression from each term of
the VALUES list is inserted into the left-most column of each new row,
and so forth for each subsequent expression. If a <span class='yyterm'>column-name</span>
list is specified, then the number of values in each term of the
VALUE list must match the number of
specified columns. Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. Table
columns that do not appear in the column list are populated with the 
<a href="lang_createtable.html#dfltval">default column value</a> (specified as part of the <a href="lang_createtable.html">CREATE TABLE</a> statement), or
with NULL if no <a href="lang_createtable.html#dfltval">default value</a> is specified.

<li><p><b>INSERT INTO </b><i>table</i><b> SELECT ...;</b>
<p>The second form of the INSERT statement contains a <a href="lang_select.html">SELECT</a> statement
instead of a VALUES clause. A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. Any SELECT statement, including
<a href="lang_select.html#compound">compound SELECTs</a> and SELECT statements with <a href="lang_select.html#orderby">ORDER BY</a> and/or <a href="lang_select.html#limitoffset">LIMIT</a> clauses, 
may be used in an INSERT statement of this form.

<li><p><b>INSERT INTO </b><i>table</i><b> DEFAULT VALUES;</b>
<p>The third form of an INSERT statement is with DEFAULT VALUES.
The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table. Each column of the new row is populated with its 
<a href="lang_createtable.html#dfltval">default value</a>, or with a NULL if no default value is specified 
as part of the column definition in the <a href="lang_createtable.html">CREATE TABLE</a> statement.

</ol>

<p>
The initial "INSERT" keyword can be replaced by
"REPLACE" or "INSERT OR <i>action</i>" to specify an alternative
constraint <a href="lang_conflict.html">conflict resolution algorithm</a> to use during 
that one INSERT command.
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".

<p>The optional "<i>schema-name</i><b>.</b>" prefix on the 
<span class='yyterm'>table-name</span>
is supported for top-level INSERT statements only.  The table name must be
unqualified for INSERT statements that occur within <a href="lang_createtrigger.html">CREATE TRIGGER</a> statements.
Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>


