1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: INSERT</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>INSERT</h2><h4><a href="syntaxdiagrams.html#insert-stmt">insert-stmt:</a></h4><blockquote> <img alt="syntax diagram insert-stmt" src="images/syntax/insert-stmt.gif"></img> </blockquote>
123
<p>The INSERT statement comes in three basic forms.
125
<li><p>The first form (with the "VALUES" keyword) creates a single new row in
126
an existing table. If no column-list is specified then the number of
127
values must be the same as the number of columns in the table. In this case
128
the result of evaluating the left-most expression in the VALUES list is
129
inserted into the left-most column of the new row, and so on. If a
130
column-list is specified, then the number of values must match the number of
131
specified columns. Each of the named columns of the new row is populated
132
with the results of evaluating the corresponding VALUES expression. Table
133
columns that do not appear in the column list are populated with the default
134
column value (specified as part of the CREATE TABLE statement), or with NULL if
135
no default value is specified.
137
<li><p>The second form of the INSERT statement contains a SELECT statement
138
instead of a VALUES clause. A new entry is inserted into the table for each
139
row of data returned by executing the SELECT statement. If a column-list is
140
specified, the number of columns in the result of the SELECT must be the same
141
as the number of items in the column-list. Otherwise, if no column-list is
142
specified, the number of columns in the result of the SELECT must be the same
143
as the number of columns in the table. Any SELECT statement, including
144
compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses,
145
may be used in an INSERT statement of this form.
147
<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
148
The INSERT ... DEFAULT VALUES statement inserts a single new row into the
149
named table. Each column of the new row is populated with its default value,
150
or with a NULL if no default value is specified as part of the column
151
definition in the CREATE TABLE statement.
155
<p>The optional conflict-clause allows the specification of an alternative
156
constraint conflict resolution algorithm to use during this one INSERT command.
157
See the section titled
158
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.
159
For compatibility with MySQL, the parser allows the use of the
160
single keyword <a href="lang_replace.html">REPLACE</a> as an
161
alias for "INSERT OR REPLACE".
163
<p>The optional "<i>database-name</i><b>.</b>" prefix on the <i>table-name</i>
164
is support for top-level INSERT statements only. The table name must be
165
unqualified for INSERT statements that occur within <a href="lang_createtrigger.html">CREATE TRIGGER</a> statements.
166
Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
167
top-level INSERT statements only and not for INSERT statements within