Publish / file · All operations

Write rows as SQL statements

A variant of Write another file format

Write a file of CREATE TABLE and INSERT statements that recreate the rows in a database.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Write rows as a CREATE TABLE and INSERT statements

SQL
CREATE TEMP TABLE items AS SELECT * FROM (VALUES
  (1, 'Alice Ng', DATE '2026-01-03', 120.50), (2, 'O''Brien', NULL, NULL), (3, 'Zoë ''Z'' Lee', DATE '2026-02-10', 7.00)) t(id, name, joined, price)
;
CREATE TEMP TABLE statements AS
SELECT 0 AS pos, 'CREATE TABLE "items" (' || (SELECT string_agg('"' || column_name || '" ' || data_type, ', ' ORDER BY column_index) FROM duckdb_columns() WHERE database_name = 'temp' AND schema_name = 'main' AND table_name = 'items') || ');' AS stmt
UNION ALL
SELECT row_number() OVER (ORDER BY id), 'INSERT INTO "items" VALUES ('
  || coalesce(id::VARCHAR, 'NULL') || ', '
  || coalesce('''' || replace(name, '''', '''''') || '''', 'NULL') || ', '
  || coalesce('''' || joined::VARCHAR || '''', 'NULL') || ', '
  || coalesce(price::VARCHAR, 'NULL') || ');'
FROM items;
COPY (SELECT stmt FROM statements ORDER BY pos) TO '<your_scratch_dir>/dump.sql' (FORMAT csv, HEADER false, QUOTE '', ESCAPE '', DELIMITER '~');
SELECT * FROM statements ORDER BY pos;
SELECT CASE WHEN ((SELECT content FROM read_text('<your_scratch_dir>/dump.sql')) = 'CREATE TABLE "items" ("id" INTEGER, "name" VARCHAR, "joined" DATE, "price" DECIMAL(5,2));' || chr(10) || 'INSERT INTO "items" VALUES (1, ''Alice Ng'', ''2026-01-03'', 120.50);' || chr(10) || 'INSERT INTO "items" VALUES (2, ''O''''Brien'', NULL, NULL);' || chr(10) || 'INSERT INTO "items" VALUES (3, ''Zoë ''''Z'''' Lee'', ''2026-02-10'', 7.00);' || chr(10)) THEN 'as expected' ELSE error('the file was not one CREATE TABLE and three INSERT statements with quotes doubled and missing values as NULL') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes dump.sql itself, in <your_scratch_dir>.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the file was not one CREATE TABLE and three INSERT statements with quotes doubled and missing values as NULL”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)
  • SQL File Outputread 2026-09-27

    Write rows to a text file as SQL INSERT statements

OpenRefine (1)
  • SQL statement exporterread 2026-09-27

    Write a .sql file of CREATE TABLE and INSERT statements with chosen column types

Pentaho Data Integration (1)
  • SQL File Outputread 2026-09-27

    Write rows to a file as SQL INSERT statements

Background

In Apache Hop, the SQL File Output transform writes input data to a text file as a set of SQL statements, in the dialect of the selected database.[1] OpenRefine's SQL exporter creates SQL containing the exported data, which can be used to overwrite or add to an existing database.[2] In PostgreSQL, pg_dump's script dumps are plain-text files of the SQL commands needed to reconstruct a database to its state when it was saved.[3]

When to use it

pg_dump's script files can reconstruct a database on other machines and architectures, and with some modifications on other SQL database products.[3] pg_dump's --inserts option dumps data as INSERT commands rather than COPY, which makes restoration very slow and is mainly useful for loading into non-PostgreSQL databases.[3] With pg_dump's --inserts, an error during restoring loses only the rows in the problematic INSERT, and the restore might fail altogether if the column order has been rearranged.[3] pg_dump's --column-inserts option is safe against column order changes, though even slower.[3] Name the columns in every INSERT, so the file still loads if the target table's columns are in a different order. (Meridian's view)

In PostgreSQL, restoring a dump executes arbitrary code of the source superusers' choice, and if they are not trusted, the dumped SQL must be inspected before restoring.[3] Write the statements for the database that will run them, and run the file once against an empty database before handing it over. (Meridian's view)

The method

In PostgreSQL, a string constant is bounded by single quotes, and a single quote inside it is written as two adjacent single quotes, as in 'Dianne''s horse'.[4] Apache Hop's SQL File Output can add a CREATE TABLE statement, and a TRUNCATE TABLE statement for a table that is expected to exist, to the generated SQL.[1] Apache Hop's SQL File Output can limit the size of a single file by starting a new one every set number of rows.[1]

In OpenRefine's SQL exporter, each exported column is given a data type, or VARCHAR, and a maximum character length where the type has one.[2] OpenRefine's SQL exporter can start the file with the creation of a table, and can include DROP and IF EXISTS.[2] In OpenRefine's SQL exporter, unchecking Allow null for a column lets a default value be set for its empty cells.[2]

Compared with the alternatives

In DuckDB, EXPORT DATABASE writes a schema.sql file of the CREATE statements needed to reconstruct the database and a load.sql file of COPY statements that read the data back from the exported files.[5] pg_dump's archive file formats, unlike its plain-text scripts, must be restored with pg_restore, which can be selective about what is restored.[3]

Sources

  1. [1]SQL File Output | Apache Hopdocumentation
  2. [2]Exporting your work | OpenRefinedocumentation
  3. [3]PostgreSQL: Documentation: 18: pg_dumpdocumentation
  4. [4]PostgreSQL: Documentation: 18: 4.1. Lexical Structuredocumentation
  5. [5]EXPORT and IMPORT DATABASE Statements – DuckDBdocumentation