1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
|
<!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='x1531' onclick='hideorshow("x1531","x1532")'>hide</button></p>
<div id='x1532' 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='x1533' onclick='hideorshow("x1533","x1534")'>show</button></p>
<div id='x1534' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram expr" src="images/syntax/expr.gif" />
<p><b><a href="syntax/filter.html">filter:</a></b>
<button id='x1535' onclick='hideorshow("x1535","x1536")'>show</button></p>
<div id='x1536' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram filter" src="images/syntax/filter.gif" />
</div>
<p><b><a href="syntax/literal-value.html">literal-value:</a></b>
<button id='x1537' onclick='hideorshow("x1537","x1538")'>show</button></p>
<div id='x1538' 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='x1539' onclick='hideorshow("x1539","x1540")'>show</button></p>
<div id='x1540' 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='x1541' onclick='hideorshow("x1541","x1542")'>show</button></p>
<div id='x1542' 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='x1543' onclick='hideorshow("x1543","x1544")'>show</button></p>
<div id='x1544' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</div>
</div>
<p><b><a href="syntax/window-defn.html">window-defn:</a></b>
<button id='x1545' onclick='hideorshow("x1545","x1546")'>show</button></p>
<div id='x1546' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram window-defn" src="images/syntax/window-defn.gif" />
<p><b><a href="syntax/frame-spec.html">frame-spec:</a></b>
<button id='x1547' onclick='hideorshow("x1547","x1548")'>show</button></p>
<div id='x1548' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram frame-spec" src="images/syntax/frame-spec.gif" />
</div>
<p><b><a href="syntax/ordering-term.html">ordering-term:</a></b>
<button id='x1549' onclick='hideorshow("x1549","x1550")'>show</button></p>
<div id='x1550' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram ordering-term" src="images/syntax/ordering-term.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/select-stmt.html">select-stmt:</a></b>
<button id='x1551' onclick='hideorshow("x1551","x1552")'>show</button></p>
<div id='x1552' 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='x1553' onclick='hideorshow("x1553","x1554")'>show</button></p>
<div id='x1554' 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='x1555' onclick='hideorshow("x1555","x1556")'>show</button></p>
<div id='x1556' 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='x1557' onclick='hideorshow("x1557","x1558")'>show</button></p>
<div id='x1558' 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='x1559' onclick='hideorshow("x1559","x1560")'>show</button></p>
<div id='x1560' 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='x1561' onclick='hideorshow("x1561","x1562")'>show</button></p>
<div id='x1562' 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='x1563' onclick='hideorshow("x1563","x1564")'>show</button></p>
<div id='x1564' 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='x1565' onclick='hideorshow("x1565","x1566")'>show</button></p>
<div id='x1566' 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='x1567' onclick='hideorshow("x1567","x1568")'>show</button></p>
<div id='x1568' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram table-or-subquery" src="images/syntax/table-or-subquery.gif" />
</div>
<p><b><a href="syntax/window-defn.html">window-defn:</a></b>
<button id='x1569' onclick='hideorshow("x1569","x1570")'>show</button></p>
<div id='x1570' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram window-defn" src="images/syntax/window-defn.gif" />
<p><b><a href="syntax/frame-spec.html">frame-spec:</a></b>
<button id='x1571' onclick='hideorshow("x1571","x1572")'>show</button></p>
<div id='x1572' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram frame-spec" src="images/syntax/frame-spec.gif" />
</div>
</div>
</div>
<p><b><a href="syntax/upsert-clause.html">upsert-clause:</a></b>
<button id='x1573' onclick='hideorshow("x1573","x1574")'>show</button></p>
<div id='x1574' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram upsert-clause" src="images/syntax/upsert-clause.gif" />
<p><b><a href="syntax/column-name-list.html">column-name-list:</a></b>
<button id='x1575' onclick='hideorshow("x1575","x1576")'>show</button></p>
<div id='x1576' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram column-name-list" src="images/syntax/column-name-list.gif" />
</div>
<p><b><a href="syntax/indexed-column.html">indexed-column:</a></b>
<button id='x1577' onclick='hideorshow("x1577","x1578")'>show</button></p>
<div id='x1578' style='display:none;' class='imgcontainer'>
<img alt="syntax diagram indexed-column" src="images/syntax/indexed-column.gif" />
</div>
</div>
<p><b><a href="syntax/with-clause.html">with-clause:</a></b>
<button id='x1579' onclick='hideorshow("x1579","x1580")'>show</button></p>
<div id='x1580' 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='x1581' onclick='hideorshow("x1581","x1582")'>show</button></p>
<div id='x1582' 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.
<p>To avoid a parsing ambiguity, the SELECT statement should always
contain a WHERE clause, even if that clause is simply "WHERE true",
if the <a href="syntax/upsert-clause.html">upsert-clause</a> is present. Without the WHERE clause, the
parser does not know if the token "ON" is part of a join constraint
on the SELECT, or the beginning of the <a href="syntax/upsert-clause.html">upsert-clause</a>.
<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.
The <a href="syntax/upsert-clause.html">upsert-clause</a> is not supported after DEFAULT VALUES.
</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>
<p>The optional "AS <span class='yyterm'>alias</span>" phrase provides an alternative
name for the table into which content is being inserted. The alias name
can be used within WHERE and SET clauses of the <a href="lang_UPSERT.html">UPSERT</a>. If there is no
<a href="syntax/upsert-clause.html">upsert-clause</a>, then the <span class='yyterm'>alias</span> is pointless, but also
harmless.
<p>See the separate <a href="lang_UPSERT.html">UPSERT</a> documentation for the additional trailing
syntax that can cause an INSERT to behave as an UPDATE if the INSERT would
otherwise violate a uniqueness constraint. The <a href="lang_UPSERT.html">upsert clause</a> is not
allowed on an "INSERT ... DEFAULT VALUES".
|
