CREATE A NEW TABLE (NOT INSERT DATA) WITH AN WITH CLAUSE

Question

I have the following query: The query is not the original because I do some calculations, but I think this gives the idea.

DROP TABLE IF EXISTS #TEMP_TREINAMENTO_RAW_DATA;
SELECT *
INTO #TEMP_TREINAMENTO_RAW_DATA
FROM [TB_STG_SAMS_TREINAMENTO_RAW_DATA];

WITH TABLE_RAW_DATA AS (
    SELECT *
    FROM #TEMP_TREINAMENTO_RAW_DATA A WITH (NOLOCK) -- 11907 CLIENTES
)

-- CREATE TABLE NEW_TABLE AS (
SELECT *
FROM TABLE_RAW_DATA A
LEFT JOIN (SELECT *
           FROM [TB_SAMS_QUADRO_FUNCIONARIOS] A WITH (NOLOCK))
           B ON A.RE = B.NR_RE;
-- )

But I am not being able to create the new table, someone can help me?

`SELECT * INTO NEW_TABLE FROM TABLE_RAW_DATA A....` Just like at the top of your script. — squillman, Sep 21 '22 at 18:31
Those `with (nolock)` hints probably don't accomplish what you think they do, and you should remove them. They're not an automatic "go faster" trick, and they can result in showing or using stale data. In fact, any time the hint does make a performance improvement you're very likely to be a in situation where it made that improvement precisely because there is stale data in play. — Joel Coehoorn, Sep 21 '22 at 18:33
Please don't write in ALL CAPS, it is harder to read, comes across as shouting and is considered rude — HoneyBadger, Sep 21 '22 at 19:33
I highly recommend reading the product documentation when you are unsure about how to do something. — Dale K, Sep 21 '22 at 20:10
Quite why you need temp tables is unclear, they seem unnecessary — Charlieface, Sep 22 '22 at 00:29

score 0 · Answer 1 · answered Sep 21 '22 at 18:32

You could use the same syntax of select ... into that you've used to create the temp table to create the new one:

DROP TABLE IF EXISTS #TEMP_TREINAMENTO_RAW_DATA;
SELECT *
INTO #TEMP_TREINAMENTO_RAW_DATA
FROM [TB_STG_SAMS_TREINAMENTO_RAW_DATA];

WITH TABLE_RAW_DATA AS (
    SELECT *
    FROM #TEMP_TREINAMENTO_RAW_DATA A WITH (NOLOCK) -- 11907 CLIENTES
)

SELECT *
INTO NEW_TABLE
FROM TABLE_RAW_DATA A
LEFT JOIN (SELECT *
           FROM [TB_SAMS_QUADRO_FUNCIONARIOS] A WITH (NOLOCK))
           B ON A.RE = B.NR_RE;

CREATE A NEW TABLE (NOT INSERT DATA) WITH AN WITH CLAUSE

1 Answers1