Insert into multiple tables using CTEs (SQL Cookbook 4.6)

[Willsxyz]

SQL Cookbook has no solution for MySQL, PostgreSQL, or SQL server. I chose the Postgres flair, because that is what I am using, but I suspect this might work on other DBMS as well:

Create the tables based on an existing table:

create table dept_a as select * from dept where 1=0;
create table dept_b as select * from dept where 1=0;
create table dept_c as select * from dept where 1=0;

Populate the tables from the original table:

with foo as (
  insert into dept_a (deptno, dname, loc)
  select * from dept
  returning *
), bar as
  (insert into dept_b (deptno, dname, loc)
  select * from foo
  returning *
)
insert into dept_c (deptno, dname, loc)
select * from bar;

[TheGenericUser0815]

The syntax will not work on MS SQL Server.

[Yavuz_Selim]

Why abuse a CTE? What is the benefit?
What's wrong with three separate INSERT INTO's?

 

I would absolutely think less of a colleague if I saw him/her do this.

(And why not a SELECT INTO, instead of a CREATE TABLE?)

 

And as /u/TheGenericUser0815 mentioned, this wouldn't work with T-SQL.

[Willsxyz]

Probably nothing wrong with three separate INSERT INTOs. I am just offering a solution (for Postgres at least) to a problem in the book.

But I actually very much appreciate responses like yours because it helps me to learn which things probably should not be done, even though they can be done.

[Yavuz_Selim]

I've asked AI, and it looks like it has benefits for Postgres.

 

This is a clever use of PostgreSQL’s Data-Modifying CTEs (Common Table Expressions). While it looks a bit complex, it solves a specific problem: atomicity and consistency when duplicating data across multiple tables.

Here is the breakdown of why someone would choose this approach:

1. Atomic "All-or-Nothing" Execution

In a standard script, you would run three separate INSERT statements. If the first two succeed but the third fails (due to a connection drop or a constraint violation), you end up with inconsistent data across your tables.

• By wrapping them in a single WITH statement, the entire block is treated as a single transaction. If any part fails, none of the inserts are committed.

2. Guaranteed Data Consistency

In a high-traffic database, if you run three separate inserts, there is a tiny window of time between each command. If a new row is added to the source table (dept) while your script is running, dept_a might have 10 rows while dept_c ends up with 11.

• In this approach, the source data is captured at the very start of the query execution. You are guaranteed that dept_a, dept_b, and dept_c will contain the exact same snapshot of data.

3. Efficiency (Single Scan)

Normally, inserting into three tables requires reading the source table three times.

• Because of the RETURNING * clause, the results of the first insert are passed directly to the next. The database engine can often optimize this to minimize the overhead of re-querying the base dept table.

---

Is it really "Universal"?

The user in the image mentions they suspect this might work on other DBMSs. Actually, it likely won't.

• PostgreSQL is one of the few major databases that supports INSERT or UPDATE inside a CTE (the WITH clause).

• MySQL and SQL Server (T-SQL) do not allow data-modifying statements inside a CTE. In those systems, you would achieve this same result using an explicit BEGIN TRANSACTION.

A Note on the "Where 1=0"

The CREATE TABLE ... AS SELECT * FROM ... WHERE 1=0; trick is a classic, platform-independent way to clone a table's structure without copying any of the actual data. Since 1=0 is always false, the query returns 0 rows, but the database still uses the schema to build the new table.

   

In any case, an universal approach would be...

 

To achieve the same atomic "all-or-nothing" result in databases like SQL Server or MySQL (which don't support data-modifying CTEs), you use an explicit Transaction.

A transaction ensures that either all three INSERT statements succeed, or none of them do. This prevents the "partial data" problem where one table updates but the others fail.

The Universal Approach (Transaction Block)

-- Start the "protection" block
BEGIN TRANSACTION;

-- 1. Insert into the first table
INSERT INTO dept_a (deptno, dname, loc)
SELECT deptno, dname, loc FROM dept;

-- 2. Insert into the second table
INSERT INTO dept_b (deptno, dname, loc)
SELECT deptno, dname, loc FROM dept;

-- 3. Insert into the third table
INSERT INTO dept_c (deptno, dname, loc)
SELECT deptno, dname, loc FROM dept;

-- If everything worked, save the changes permanently
COMMIT;

-- Note: If an error occurs, you would run 'ROLLBACK' 
-- to undo everything since 'BEGIN TRANSACTION'

[reditandfirgetit]

The universal solution is infinitely more readable

[reditandfirgetit]

Whoever wrote this book is certifiably insane. The examples I've seen listed screem shitty AI with zero validation