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

Roberta_Gomes 0
Viewed 36

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?

sql-server sql
1 Answers

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;
Jorge Bugal0
Related