How to Duplicate a Table in ClickHouse
Last updated July 25, 2026 · By the SaturnSQL team
CREATE TABLE new AS old copies the structure and engine but no data; follow it with INSERT INTO new SELECT * FROM old to copy the rows. For partitioned tables, ATTACH PARTITION FROM copies whole partitions without rewriting data.
CREATE TABLE ... AS copies the column definitions, the engine, and the ORDER BY and PARTITION BY clauses, but the new table starts empty. The INSERT is a full read and write of every row, so on a large table it costs real time and roughly doubles the disk used.
CREATE TABLE events_backup AS events;
INSERT INTO events_backup SELECT * FROM events;Copy a partition without rewriting
ATTACH PARTITION FROM moves data by hardlinking the underlying parts rather than rewriting them, so it is close to instant regardless of partition size. The two tables must have identical structure, partition key, and engine. Run SELECT DISTINCT partition FROM system.parts WHERE table = 'events' to see which partition IDs exist.
ALTER TABLE events_backup ATTACH PARTITION 202607 FROM events;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free