copy

sqlload copy SOURCE DESTINATION [OPTIONS]

sqlload copy copies one table once, through the same plan and load as a project. It needs no sqlload.toml.

$ sqlload copy orders/ duckdb://shop.duckdb#raw.orders
✓ Plan complete  1 selected, 1 object to create, 1 to load   (sqlload plan shows the tree)

created  raw.orders  9 columns
loaded  raw.orders      3 rows 0.0s

✓ Completed  1 loaded, 3 rows, 0.1s
├── slowest      raw.orders 0.0s
└── peak memory  73.9 MiB

Endpoints

Each end is a connection named in sqlload.toml, or a URI:

Form	As source	As destination
connection:schema.table	A table of a named Postgres connection	A table of a named Snowflake or DuckDB connection
postgresql://user:${ENV:PGPASSWORD}@host:5432/db#schema.table	A Postgres table; add ?sslmode=... and the other TLS settings as a query
duckdb://file.duckdb#SCHEMA.TABLE		A DuckDB table
s3://bucket/prefix/?region=eu-west-2	Files in S3	Parquet files in S3
file:///folder, or a folder	Files in a folder	Parquet files in a folder

On Windows a folder can be a drive path (C:\data\orders) or a network share (\\server\share\orders), and the URL forms are file:///C:/data/orders and file://server/share/orders.

Snowflake needs auth and a load_method, which a URI can’t carry, so a Snowflake end is always a named connection.

Options

Option	Meaning
--write-strategy <STRATEGY>	append, merge, delete_insert or replace. By default replace without a cursor, and merge with a cursor and a key; with a cursor and no key, as from files, set this or --unique-key. --print-toml writes the strategy out, as a project needs it set.
--unique-key <COLUMN>...	The key for merge and delete_insert. A Postgres table’s primary key by default, written unique_key = "{primary_key}" by --print-toml.
--cursor-column <COLUMN>	The column incremental copies continue from.
--format <FORMAT>	A files source’s format: parquet, ndjson, json or csv.
--print-toml	Print the copy as a project entry for sqlload.toml instead of running it.

$ sqlload copy 'postgresql://postgres:${ENV:PGPASSWORD}@localhost:5432/shop#public.orders' duckdb://shop.duckdb#raw.orders --cursor-column updated_at
✓ Plan complete  1 selected, 1 object to create, 1 to load   (sqlload plan shows the tree)

created  raw.orders  7 columns
loaded  raw.orders  1,000 rows 0.0s

✓ Completed  1 loaded, 1,000 rows, 0.1s
├── slowest      raw.orders 0.0s
└── peak memory  80.1 MiB

Run it again and it continues from the destination’s maximum updated_at, as a project would.

Passwords

A password written into a URI never reaches printed projects, plans or errors. copy warns that ${ENV:NAME} in the URI keeps it out of shell history; quote the URI so the shell leaves it alone. --print-toml refers to a literal password as ${ENV:SQLLOAD_COPY_SOURCE_PASSWORD} (or SQLLOAD_COPY_DESTINATION_PASSWORD).

Turning a copy into a project

$ sqlload copy orders/ duckdb://shop.duckdb#raw.orders --print-toml
# Written by `sqlload copy --print-toml`; append it to sqlload.toml (it adds a target, a source and any URI connections) to load this table on every run.

[connections.copy_orders_destination]
database = "shop.duckdb"
kind = "duckdb"

[connections.copy_orders_source]
kind = "filesystem"
root = "orders/"

[sources.copy_orders]
connection = "copy_orders_source"
description = "Copied from folder orders/."
destination = "copy_orders"
kind = "files"
path = ""
schema = "raw"
table_name = "orders"
write_strategy = "replace"

[targets.copy_orders]
connection = "copy_orders_destination"
loader_schema = "raw"

Names sqlload.toml already has get a suffix, so several printed copies can be appended.