Dynamic SQL in DuckDB CLISQL

Editor's note: this gets a little recursive for all those SQL nerds out there. It shows you how to use SQL to generate SQL. It also shows you some of the dot commands for interacting with the DuckDB CLI: changing the output format and writing SQL to a file which is then read in for execution.

Execute this SQL

-- Use SQL to generate SQL statements.
-- Write them to a file, then execute that file.
-- This uses DuckDB CLI-specific features, so it only works in the CLI.
.mode list
.header off
.once my_dynamic_sql.sql
select 'CREATE TABLE t' || i || ' AS SELECT ' || i || ' AS my_column;' from generate_series(5) x(i);
.read my_dynamic_sql.sql

select table_name from duckdb_tables(); -- Check our work!
Copy code

Alex Monahan

Edited 02/23/24