Getting Data In and Out in Bulk

Lora's old spreadsheet still holds the one thing the Marquee cannot afford to lose: 800 customers, collected over two years of Thursday regulars and school-holiday matinees. All of them need to move into the customers table, and nobody is going to type 800 INSERT statements by hand. This is not a corner case — every real database project begins with data that already exists somewhere else.

So every database ships bulk doors: a way to pour a whole file in, and a way to pour a query result out. Both doors speak the same plain format, called CSV, and this page moves the Marquee's customers through the first door and a report for Lora's accountant through the second.

CSV in One Paragraph

CSV stands for comma-separated values, and it earns the name completely: a plain text file, one row per line, values separated by commas, with the first line very often carrying the column names. Open one in a plain text editor once and it holds no further mysteries:

name,email,joined_on
Priya N,priya@example.com,2026-07-08
Marcus T,marcus@example.com,2024-03-15

One honest warning explains half of all import problems in advance: CSV carries no types. Everything in that file is text. The 2024-03-15 on the last line is not a date — it is ten characters that look like one, and the same is true of every number and timestamp in any CSV ever written. Types get re-imposed by the table on the way in. Hold that thought; it returns in a moment with twelve casualties.

The Bulk Door In

Moving house, you do not carry possessions to the truck one spoon at a time — you pack boxes, because one-at-a-time does not survive contact with 800 of anything. The import command is the truck: it takes the whole file in one trip. That is the entire analogy; back to the commands.

The idea is the same in every engine: point the command at the file, map the file's columns to the table's columns, run. The spelling, though, genuinely differs — this is one of the places where SQL's portability honestly runs out, so here are all three doors, labeled. Recognize them; memorize none:

COPY customers (name, email, joined_on)
FROM 'customers.csv' CSV HEADER;

.mode csv
.import --skip 1 customers.csv customers

LOAD DATA INFILE 'customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

All three do the same job: read the file line by line, split on commas, convert each value to its column's type, and insert the rows far faster than separate INSERT statements would manage. The HEADER, --skip 1, and IGNORE 1 LINES parts all say the same thing in three dialects — the first line is column names, not a customer named "name". Note that SQLite's version is not SQL at all but a command of its command-line shell; you will meet this split again whenever tools grow up alongside a language.

When Rows Fail the Audition

Here is the part beginners do not expect: the bulk door has the same bouncer as topic 21. Import is a faster line into the club, not a back door around the checks — every row is auditioned against the table's types and constraints exactly as if it had arrived in its own INSERT.

Of Lora's 800 rows, 788 go in and 12 are rejected: a handful with a blank email cell, which NOT NULL refuses; several with the same address entered twice over the years, which UNIQUE refuses; and one whose joined_on reads March sometime — ten characters that a DATE column will not accept as a date. This is the CSV warning paying off: the file happily stored all of it as text, and the table re-imposed the truth at the door.

Where the 12 surface depends on the tool. Some doors stop at the first bad row and load nothing until the file is clean — PostgreSQL's COPY works this way, all or nothing. Others load the good rows and set the bad ones aside with reasons, which is what SQLite's shell and most graphical import tools do. Either way the professional response is the same: read the reasons, fix the file, run again. The tempting alternative — loosening the table's rules so the import "just works" — means importing the spreadsheet's diseases along with its data, and Chapter 1 already showed where that leads. The 12 rejects are not a problem with the import. They are two years of quiet spreadsheet rot, finally caught.

The Door Out

Exports are the same door swinging the other way, and simpler: any SELECT can become a CSV. Lora's accountant wants the customer list each quarter, and the report is nothing more than a query with an export wrapper around it:

COPY (SELECT name, email, joined_on
      FROM customers
      ORDER BY joined_on)
TO 'customer-report.csv' CSV HEADER;

Everything you learned in Chapters 3 and 4 applies inside those parentheses — WHERE, JOINs, GROUP BY, all of it. Any question you can ask the database, you can hand to someone as a file. Other engines offer the same move under different spellings, and every graphical database tool has an "export result" button doing exactly this.

With that, a loop that opened in Chapter 1 quietly closes: spreadsheet → database → spreadsheet. But the direction of truth has reversed. The database is now the source of truth, and every exported file is a copy — a snapshot that starts going stale the moment it is written. When a copy drifts, nobody edits it back into shape; you regenerate it from the source. Chapter 1's drifting-copies failure was never fixed by forbidding copies. It is fixed by everyone knowing, without ambiguity, which one is the original.