Shape / rows · All operations

Insert rows

A variant of Edit cells by hand

Add blank, constant or supplied rows at a chosen position.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Insert blank, constant and supplied rows at chosen positions

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE numbered AS
SELECT row_number() OVER (ORDER BY order_id) AS pos, order_id, customer, amount FROM orders;
-- each patch row says which position it follows; rows that follow the same position keep their `seq` order
CREATE TEMP TABLE patch AS SELECT * FROM (VALUES
  (2, 1, NULL::INTEGER, NULL::VARCHAR, NULL::DECIMAL(5, 2)),   -- a blank row after row 2
  (5, 1, 0,             'Subtotal',    0.00),                  -- a constant row after row 5
  (8, 1, 101,           'Gus Hall',    12.00),                 -- supplied rows after row 8
  (8, 2, 102,           'Hana Iyer',   34.00)
) t(after_pos, seq, order_id, customer, amount);
CREATE TEMP TABLE result AS
SELECT row_number() OVER (ORDER BY place, seq) AS pos, order_id, customer, amount
FROM (SELECT pos AS place, 0 AS seq, order_id, customer, amount FROM numbered
      UNION ALL
      SELECT after_pos + 0.5, seq, order_id, customer, amount FROM patch);
SELECT * FROM result ORDER BY pos;
SELECT CASE WHEN ((SELECT count(*) FROM result) = 14 AND (SELECT list(order_id ORDER BY pos) FROM result) IS NOT DISTINCT FROM [1, 2, NULL, 3, 4, 5, 0, 6, 7, 8, 101, 102, 9, 10] AND (SELECT customer FROM result WHERE pos = 3) IS NULL AND (SELECT customer FROM result WHERE pos = 7) = 'Subtotal' AND (SELECT list(customer ORDER BY pos) FROM result WHERE pos IN (11, 12)) = ['Gus Hall', 'Hana Iyer']) THEN 'as expected' ELSE error('the result was not the ten orders with a blank row at 3, a Subtotal row at 7 and two supplied rows at 11 and 12') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the result was not the ten orders with a blank row at 3, a Subtotal row at 7 and two supplied rows at 11 and 12”.
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).

KNIME Analytics Platform (1)

Constant Value Row Appenderread 2026-09-27
Append a given number of rows filled with missing values or a constant

OpenRefine (1)

Add blank rowsread 2026-09-27
Insert blank rows: prepend one, append one, or add several

Power Query M (1)

Table.InsertRowsread 2026-09-27
Insert a list of rows at a given position

VisiData (2)

add-rowread 2026-09-23
Insert a blank row
add-rowsread 2026-09-23
Insert N blank rows

Background

In Power Query, Table.InsertRows returns a table with a list of rows inserted into it at a given position, its offset.^[1] An SQL INSERT statement adds one or more records to any single table in a relational database.^[2] KNIME's Constant Value Row Appender adds a set number of rows, filled with missing values or a given constant, to its input table.^[3]

When to use it

In KNIME, adding empty rows can be useful in a report table to make sure it has a minimum number of rows, which are then shown as blank rows.^[3] Inserting rows suits output a person reads by position: a blank spacer between sections, a subtotal line under a block, a handful of records that were missing from an export. (Meridian's view) For rows that belong to a key rather than a place, such as a missing customer, add them to the data and let sorting decide where they appear. (Meridian's view)

In the relational model the tuples of a relation's body appear in no particular order, and a similar point applies to the rows of an SQL table.^[4] A position is only meaningful once the table's order has been pinned down, so number the rows by a column that orders them before deciding where new rows go. (Meridian's view) In DuckDB, ORDER BY does not guarantee that the original row order is preserved, because it may not use a stable sorting algorithm.^[5] Give rows that land at the same position a tie-breaking number of their own, so their order does not depend on the sort. (Meridian's view) DuckDB's preserve_insertion_order setting is true by default, and when it is turned off DuckDB may reorder the rows of a query with no ORDER BY before they reach a table or file.^[5] In Power Query, each column of a row passed to Table.InsertRows must match the column types of the table.^[1]

The method

In DuckDB, row_number() OVER () turns the original row order into an explicit column that later operations can reference, and on a materialised table the rowid pseudo-column does the same.^[5] DuckDB guarantees that UNION ALL preserves the original row order, while UNION and JOIN do not.^[5] Numbering the existing rows, giving each new row the number it should follow, combining the two and renumbering in order of those numbers inserts rows at chosen places in plain SQL. (Meridian's view)

In DuckDB, INSERT INTO adds one or more rows given by value expressions, or zero or more rows that a query returns.^[6] In DuckDB, each column left out of an INSERT's column list is filled with its declared default value, or NULL if it has none.^[6]

Compared with the alternatives

KNIME's Constant Value Row Appender appends its rows to the input table, while Power Query's Table.InsertRows places its rows at a chosen offset.^[3]^[1] DuckDB's MERGE INTO can insert a source row when it matches no row of the destination and update the matching row when it does.^[7] An append or a MERGE adds rows by what they are; an insert at a position adds them by where they sit, and only the latter needs the row order settled first. (Meridian's view)

Sources

[1]Table.InsertRows - PowerQuery M | Microsoft Learndocumentation
[2]Insert (SQL) - Wikipediasecondary source
[3]Constant Value Row Appenderdocumentation
[4]Relation (database) - Wikipediasecondary source
[5]Order Preservation – DuckDBdocumentation
[6]INSERT Statement – DuckDBdocumentation
[7]MERGE INTO Statement – DuckDBdocumentation