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.

min_qty
stage_csv_lines.sql
Replay: real traced execution (multi-file project)
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 (2)) 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 (1)) 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 (4)) 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;

  1. tables ← 1 row

    1CREATE TABLE raw_lines (line TEXT);2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');
    values this step1 rowtables

  2. raw_lines ← 3 rows

    1CREATE TABLE raw_lines (line TEXT);2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');3CREATE TABLE inventory (name TEXT, qty INTEGER);
    values this step3 rowsraw_lines

  3. tables ← 2 rows

    2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');3CREATE TABLE inventory (name TEXT, qty INTEGER);4WITH params(min_qty) AS (VALUES (2)) 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);
    values this step2 rowstables

  4. inventory ← 2 rows

    3CREATE TABLE inventory (name TEXT, qty INTEGER);4WITH params(min_qty) AS (VALUES (2)) 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);5SELECT name, qty FROM inventory ORDER BY name;
    values this step2 rowsinventory

  5. result ← 2 rows

    4WITH params(min_qty) AS (VALUES (2)) 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);5SELECT name, qty FROM inventory ORDER BY name;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE raw_lines (line TEXT);2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');
    values this step1 rowtables

  2. raw_lines ← 3 rows

    1CREATE TABLE raw_lines (line TEXT);2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');3CREATE TABLE inventory (name TEXT, qty INTEGER);
    values this step3 rowsraw_lines

  3. tables ← 2 rows

    2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');3CREATE TABLE inventory (name TEXT, qty INTEGER);4WITH params(min_qty) AS (VALUES (1)) 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);
    values this step2 rowstables

  4. inventory ← 3 rows

    3CREATE TABLE inventory (name TEXT, qty INTEGER);4WITH params(min_qty) AS (VALUES (1)) 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);5SELECT name, qty FROM inventory ORDER BY name;
    values this step3 rowsinventory

  5. result ← 3 rows

    4WITH params(min_qty) AS (VALUES (1)) 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);5SELECT name, qty FROM inventory ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE raw_lines (line TEXT);2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');
    values this step1 rowtables

  2. raw_lines ← 3 rows

    1CREATE TABLE raw_lines (line TEXT);2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');3CREATE TABLE inventory (name TEXT, qty INTEGER);
    values this step3 rowsraw_lines

  3. tables ← 2 rows

    2INSERT INTO raw_lines VALUES ('notebook,3'), ('pen,1'), ('lamp,4');3CREATE TABLE inventory (name TEXT, qty INTEGER);4WITH params(min_qty) AS (VALUES (4)) 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);
    values this step2 rowstables

  4. inventory ← 1 row

    3CREATE TABLE inventory (name TEXT, qty INTEGER);4WITH params(min_qty) AS (VALUES (4)) 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);5SELECT name, qty FROM inventory ORDER BY name;
    values this step1 rowinventory

  5. result ← 1 row

    4WITH params(min_qty) AS (VALUES (4)) 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);5SELECT name, qty FROM inventory ORDER BY name;
    values this step1 rowresult
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`.