CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
wilsonpage
wilsonpage

Reputation: 17610

How to use multiple WITH statements and multiple INSERT statements in the same query

I'm struggling to use WITH in combination with INSERT. I've massively simplified the queries for sake of example.

I know this can be written without WITH, but if I want to use WITH and multiple INSERT what syntax should I use?

WITH table_1_items AS (
  SELECT * FROM example_table_1
),
table_2_items AS (
  SELECT * FROM example_table_2
)

INSERT INTO other_table_1 FROM table_1_items;
INSERT INTO other_table_2 FROM table_2_items;

Results in error:

relation "table_2_items" does not exist

Upvotes: 1

Views: 1070

Answers (1)

user330315
user330315

Reputation:

The INSERTs need to be part of the same CTE:

WITH table_1_items AS (
  SELECT * FROM example_table_1
),
table_2_items AS (
  SELECT * FROM example_table_2
), 
insert_table_1 as (
  INSERT INTO other_table_1 FROM table_1_items
)
INSERT INTO other_table_2 FROM table_2_items;

Upvotes: 1

Related Questions