In most dumps a row is written as part of an INSERT statement. The statement
names a table, can name its columns, and gives one or more lists of values.
The forms a row takes
-- values for every column, in the table's own order
INSERT INTO fruit VALUES (1, 'apple');
-- the columns named, so the order and the set can differ
INSERT INTO fruit (name, id) VALUES ('pear', 2);
-- several rows in one statement
INSERT INTO fruit VALUES (3, 'plum'), (4, 'quince');A value can be a literal of any kind, NULL, or an expression. See
Literals.
What each tool writes
| Tool | Form | Notes |
|---|---|---|
| mysqldump | INSERT INTO `t` VALUES (…),(…); | Many rows to a statement. A new statement starts when the current one reaches the size set by --net-buffer-length. --skip-extended-insert writes one row each, and --complete-insert names the columns. |
| phpMyAdmin | INSERT INTO `t` (`a`, `b`) VALUES, then a row to a line | Names the columns. |
| pg_dump | Uses COPY instead | With --inserts it writes INSERT statements, with --column-inserts it names the columns, and --rows-per-insert puts several rows in each. |
sqlite3 .dump | INSERT INTO t VALUES(…); | One row to a statement. A table’s name can be in double quotes. |
| SQL Server scripts | INSERT [dbo].[t] ([a]) VALUES (…) | One row to a statement, often without INTO or a semicolon. |
Other forms
These appear in hand-written scripts more than in dumps.
| Form | Dialect |
|---|---|
INSERT … SELECT … | All: rows copied from a query |
INSERT … DEFAULT VALUES | Standard SQL: one row of defaults |
INSERT … SET a = 1, b = 2 | MySQL only |
INSERT … ON DUPLICATE KEY UPDATE … | MySQL only |
INSERT … ON CONFLICT DO NOTHING | PostgreSQL and SQLite |
REPLACE INTO … | MySQL and SQLite |
Size
Nothing in SQL limits how many rows a statement holds. In practice a server limits the
size of one statement (MySQL’s max_allowed_packet, for example), which is
why a tool that writes many rows to a statement also starts a new one from time to
time.