Import and Export Concepts

Stage Rows

Load Text Before Tables

An import workflow can stage raw text lines, then transform accepted rows into a typed table.

Program

Play the script to choose the minimum quantity accepted from staged comma-separated lines.

stage_csv_lines.sql 
CREATE TABLE raw_lines (line TEXT);
INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');
CREATE TABLE inventory (name TEXT, qty INTEGER);
WITH params(min_qty) AS (VALUES ()) INSERT INTO inventory SELECT substr(line, 1, instr(line, ',') - 1) AS name, CAST(substr(line, instr(line, ',') + 1) AS INTEGER) AS qty FROM raw_lines WHERE CAST(substr(line, instr(line, ',') + 1) AS INTEGER) >= (SELECT min_qty FROM params);
SELECT name, qty FROM inventory ORDER BY name;

CREATE TABLE raw_lines (line TEXT);
INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');
CREATE TABLE inventory (name TEXT, qty INTEGER);
WITH params(min_qty) AS (VALUES ()) INSERT INTO inventory SELECT substr(line, 1, instr(line, ',') - 1) AS name, CAST(substr(line, instr(line, ',') + 1) AS INTEGER) AS qty FROM raw_lines WHERE CAST(substr(line, instr(line, ',') + 1) AS INTEGER) >= (SELECT min_qty FROM params);
SELECT name, qty FROM inventory ORDER BY name;

CREATE TABLE raw_lines (line TEXT);
INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');
CREATE TABLE inventory (name TEXT, qty INTEGER);
WITH params(min_qty) AS (VALUES ()) INSERT INTO inventory SELECT substr(line, 1, instr(line, ',') - 1) AS name, CAST(substr(line, instr(line, ',') + 1) AS INTEGER) AS qty FROM raw_lines WHERE CAST(substr(line, instr(line, ',') + 1) AS INTEGER) >= (SELECT min_qty FROM params);
SELECT name, qty FROM inventory ORDER BY name;
staging table Raw import text is kept separate from the final typed table.
transform `substr` and `instr` split each comma-separated line into columns.
load filter The selector changes which parsed rows are accepted into `inventory`.