# Append tables with a source column

Join / union

A variant of [Append tables](https://meridian.online/operations/append-tables).

Stack the rows of several tables and add a column that records which table each row came from.

Page: <https://meridian.online/operations/append-tables/append-with-a-source-column>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

append tables with a column naming the source

```sql
CREATE TEMP TABLE t1 AS SELECT * FROM (VALUES (1, 'a'), (2, 'b')) t(id, v);
CREATE TEMP TABLE t2 AS SELECT * FROM (VALUES (2, 'b'), (3, 'c')) t(id, v);
COPY t1 TO '<your_scratch_dir>/tag_one.csv' (HEADER);
COPY t2 TO '<your_scratch_dir>/tag_two.csv' (HEADER);
-- tables: a literal for each; the row (2, b) is in both and stays twice
CREATE TEMP TABLE from_tables AS
SELECT 't1' AS source, * FROM t1 UNION ALL BY NAME SELECT 't2' AS source, * FROM t2;
-- files: the reader adds the path of the file each row came from (filename = true); only its name is kept
CREATE TEMP TABLE from_files AS
SELECT parse_filename(filename) AS source, id, v
FROM read_csv(['<your_scratch_dir>/tag_one.csv', '<your_scratch_dir>/tag_two.csv'], filename = true);
SELECT * FROM from_tables ORDER BY source, id;
SELECT * FROM from_files ORDER BY source, id;
SELECT CASE WHEN ((SELECT list((source, id) ORDER BY source, id) = [('t1', 1), ('t1', 2), ('t2', 2), ('t2', 3)] FROM from_tables)
  AND (SELECT list((source, id, v) ORDER BY source, id) = [('tag_one.csv', 1, 'a'), ('tag_one.csv', 2, 'b'), ('tag_two.csv', 2, 'b'), ('tag_two.csv', 3, 'c')] FROM from_files)
  AND (SELECT list(column_name) FROM (DESCRIBE from_tables)) = ['source', 'id', 'v']) THEN 'as expected' ELSE error('each of the 4 rows should name the table or file it came from, the row found in both tables should appear once for each, and the source column should come first') 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 `tag_one.csv` and `tag_two.csv` 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).

- dbt-utils: [`union_relations`](https://raw.githubusercontent.com/dbt-labs/dbt-utils/main/README.md) (read 2026-09-27)
- Power Query M: [`Table.FromPartitions`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#row-operations) (read 2026-09-27)