SQL export multiple CTE tables to a new table

Question

I have created two CTE tables in my query which look at my data at different levels during 1 day. There is bunch of data added and everything runs smoothly. If I add SELECT * FROM level1_cte to the end it will produce correct output.

Q1: Why can't I query all of my cte tables at once? They don't depend on each others.

WITH level1 
AS 
(
SELECT [order] FROM test
),
level2
([route], tag)
AS (
SELECT
[route],
tag
FROM test
)

SELECT * FROM level1
SELECT * FROM level2

This will produce error and I need to comment out one of the tables to be able to look at only one.

Q2: I would like to write the data from these 2 tables to a three permanent tables (level1, level2). Is that possible or do I need to duplicate and create two different procedures from this query?

DMBS is SQL Server

DATA:

create table test (
"date" date,
"order" varchar(10),
route varchar(10),
tag varchar(10),
test varchar(10))

insert into test values
('2022-01-01','A','AB','NA','AB'), 
('2022-01-01','A','AB','NA','AB'), 
('2022-01-01','A','AB','NA','AB'), 

('2022-01-01','B','BB','NA','AB'),  
('2022-01-01','B','BB','NA','AB'),
('2022-01-01','B','BB','this','AB')

Because it doesn't work that way. But what would be the advantage here anyway? — shawnt00, Apr 12 '22 at 08:52
@shawnt00 "what would be the advantage here anyway?" - Retrieving object-graphs from multiple tables is a common use-case (though that's unrelated to the OP's question). — Dai, Apr 12 '22 at 08:54
`create table test` <-- Why aren't any of your columns marked `NOT NULL`? And where is your `PRIMARY KEY`? — Dai, Apr 12 '22 at 08:56
@Dai, there isn't any repetition of the logic so no reason to consolidate the CTEs together. — shawnt00, Apr 12 '22 at 08:56
So I'm not able to write these tables to new table, but need to create individual queries? I think advantage would be able to more stuff with one query, these are heavily linked and I'm using same table as a starting point, but creating different angles on my data. I think it would be usefull to save results from this to new place. — Hakki, Apr 12 '22 at 08:56
@shawnt00 You are correct, I just noticed that `level1` and `level2` are unrelated to each other. — Dai, Apr 12 '22 at 08:57
And this is only toy example, real data needs to be kept on different tables as it is looking at different levels of the data, which is cleaned from one big data dump. I have done it with CTE as they work pretty well, now just looking idea how to save results from this query, so I don't have to create 2-3 same procedures where last statement is different. — Hakki, Apr 12 '22 at 09:00
@Hakki "And this is only toy example, real data needs to be kept on different tables as it is looking at different levels of the data, which is cleaned from one big data dump" - **please don't show us oversimplified data designs** - it means that I've just wasted the past 10 minutes writing my answer because the design of SQL queries depends _very much_ on the **real** DB design (it's fine to use dummy _values_, but don't try to hide multiple source tables and other things like that because otherwise our answers will be useless to you and anyone else reading this QA post) — Dai, Apr 12 '22 at 09:02
I think data is enough to show if my questions can be achieved. But I got your point that CTE doesn't work as I might have expected. As I stated, I don't want to JOIN this data to 1 table, but would like to save result to 2 new tables. If that is impossible I'm fine with it. — Hakki, Apr 12 '22 at 09:11
@Hakki If you want to redirect each `SELECT`'s data to separate tables (i.e. `WITH cte1 ( etc ) INSERT INTO table1 ( x, y, z ) SELECT x, y, z FROM cte1; WITH cte2 ( etc ) INSERT INTO table2 ( a, b, c ) SELECT a, b, c FROM cte2;`) then you should delete this question and post a new one, because that is completely removed from what you actually posted. — Dai, Apr 12 '22 at 10:31

Dai · Accepted Answer · 2022-04-12T09:09:12.930

Why can't I query all of my cte tables at once? They don't depend on each others.

Consider these things:

While a single query can have multiple CTEs, a single CTE cannot be shared by multiple queries.
- I agree this is annoying.
Each top-level SELECT statement represents a completely separate query.
- (Confusingly, CTEs (WITH x AS ( ... )) are placed before the SELECT keyword, however it is still a single query.
- Therefore your SELECT * FROM level1 SELECT * FROM level2 statements are two separate queries.
  - Only the first query (SELECT * FROM level1) has access to the level1 and level2 CTEs defined before it.
- This is why you should always use a terminating semicolon so you can not-only visually see the syntactical bounds of each statement and query, but also prevent you from making this kind of mistake again in future.
  - So it should have been formatted as:
```
------------------
-- Query 1:
------------------
WITH level1 AS (
     SELECT [order] FROM test
),
level2 AS AS (
     SELECT [route], [tag] FROM test
)
SELECT * FROM level1;

------------------
-- Query 2 (which is *entirely separate* from Query 1 above):
------------------
SELECT * FROM level2; /* <-- Error: The `level2` CTE isn't in-scope! */
```

You can combine two or more SELECT queries into one by concatenating their results using UNION ALL, however you can only do this for queries with the same column-design, but your two SELECT statements have different columns ([order]) vs ([route] and [tag]) so it's nonsensical to concatenate them as-is.

But if you do want to concatenate them, using NULL for their missing columns' data, then do this:

WITH level1 AS (
    SELECT
        [order]
    FROM
        test
),
level2 AS AS (
    SELECT
        [route],
        [tag]
    FROM
        test
)
SELECT
    NULL AS [route],
    NULL AS [tag],
    [order]
FROM
    level1

UNION ALL

SELECT
    [route],
    [tag]
    NULL AS [order]
FROM
    level2;    /* <-- Note the semicolon. */

SQL export multiple CTE tables to a new table

1 Answers1