Documentation Source Text

Check-in [89ca937f0e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Attempt to improve the INSERT documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 89ca937f0ea167388480f36c21c9487227da819e7ab641e7abd1a91396612149
User & Date: drh 2017-07-01 23:23:01.442
Context
2017-07-10
19:55
Merge fixes from the 3.19 branch. (check-in: fea84d9dc3 user: drh tags: trunk)
2017-07-01
23:24
Attempt to improve the INSERT documentation. (check-in: 6b1a2005d8 user: drh tags: branch-3.19)
23:23
Attempt to improve the INSERT documentation. (check-in: 89ca937f0e user: drh tags: trunk)
2017-06-30
00:47
Add the "fts5ext.so" target for building an SQLite extension that enables the sqlite3 command-line shell to access the website search database. (check-in: 4110cb10fc user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
3235
3236
3237
3238
3239
3240
3241
3242

3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
3257
3258
3259

3260
3261
3262
3263
3264
3265
3266
3267
3268
3269

3270
3271
3272
3273
3274
3275
3276
3277


3278

3279
3280
3281
3282
3283
3284
3285
3286
3287
##############################################################################
Section INSERT insert {INSERT *INSERTs}

RecursiveBubbleDiagram insert-stmt
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>

<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If the <yyterm>column-name</yyterm> list after
<yyterm>table-name</yyterm> 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 <yyterm>column-name</yyterm>
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 
[default column value] (specified as part of the [CREATE TABLE] statement), or
with NULL if no [default value] is specified.


<li><p>The second form of the INSERT statement contains a [SELECT] 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
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.


<li><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 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.

</ul>



<p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative

constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled [ON CONFLICT] for additional information.
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 
<yyterm>table-name</yyterm>
is supported for top-level INSERT statements only.)^  ^The table name must be







|
>
|
















>
|









>
|





|

>
>
|
>
|
<







3235
3236
3237
3238
3239
3240
3241
3242
3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285

3286
3287
3288
3289
3290
3291
3292
##############################################################################
Section INSERT insert {INSERT *INSERTs}

RecursiveBubbleDiagram insert-stmt
</tcl>

<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 <yyterm>column-name</yyterm> list after
<yyterm>table-name</yyterm> 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 <yyterm>column-name</yyterm>
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 
[default column value] (specified as part of the [CREATE TABLE] statement), or
with NULL if no [default value] is specified.

<li><p><b>INSERT INTO </b><i>table</i><b> SELECT ...;</b>
<p>The second form of the INSERT statement contains a [SELECT] 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
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] 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 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.

</ol>

<p>
^The initial "INSERT" keyword can be replaced by
"REPLACE" and "INSERT OR <i>action</i>" to specify an alternative
constraint [ON CONFLICT|conflict resolution algorithm] 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 
<yyterm>table-name</yyterm>
is supported for top-level INSERT statements only.)^  ^The table name must be