# Write rows as SQL statements

Publish / file

A variant of [Write another file format](https://meridian.online/operations/write-another-file-format).

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

Page: <https://meridian.online/operations/write-another-file-format/write-rows-as-sql-statements>

## Status

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.

## What other tools call it

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

- Apache Hop: [`SQL File Output`](https://hop.apache.org/manual/latest/pipeline/transforms/sqlfileoutput.html) (read 2026-09-27)
- OpenRefine: [`SQL statement exporter`](https://openrefine.org/docs/manual/exporting#sql-exporter) (read 2026-09-27)
- Pentaho Data Integration: [`SQL File Output`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)