SQL export multiple CTE tables to a new table-CodePudding

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')

CodePudding user response：

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. */