[解決済み] SQLの "With "句を使用してテーブルを作成する方法

質問

WITH句を使用して永続テーブルを作成しようとしていますが、エラーが発生します。

ちなみに、現在私が見つけた回答は

CREATE TABLE my_table
AS 
WITH my_tables_data AS ( 
   SELECT another_table.data1 AS some_value 
   FROM another_table
) 
SELECT * 
FROM some_data; 

しかし、次のようなエラーが発生します。

Msg 319, Level 15, State 1, Line 5 キーワード付近の構文が正しくありません。 'with'です。このステートメントが共通のテーブル式、または xmlnamespaces 節または変更追跡コンテキスト節では、直前の 文はセミコロンで終了させなければなりません。

私のコードは

CREATE TABLE SalesOrdersPerYear  
WITH t1 AS (
    -- Define the CTE expression name and column list.  
    WITH Sales_CTE (SalesPersonID, BaseSalary)  
    AS  
    -- Define the CTE query.  
    (  
        SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
        FROM SALES_PERSON  
        WHERE SALES_PERSON_ID IS NOT NULL  
    )  
    -- Define the outer query referencing the CTE name.  
    SELECT SalesPersonID, BaseSalary AS TotalSales  
    FROM Sales_CTE   
    ORDER BY SalesPersonID, BaseSalary;  
)

どなたかご指導いただけないでしょうか。

ありがとうございました。

どのように解決するのですか？

これは、SPLサーバでは有効な構文ではありません。 CREATE TABLE を作成し、カラム名と型を指定するか、あるいは SELECT INTO 文にデータを含めることができます。

アプローチ1：テーブルを作成してから、入力する。

CREATE TABLE SalesOrdersPerYear 
( SalesPersonID int, BaseSalary float)
;

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
insert into SalesOrdersPerYear  
SELECT SalesPersonID, BaseSalary AS TotalSales  
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

アプローチ2 - ワンステップで完了

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
select  SalesPersonID, BaseSalary AS TotalSales  
into SalesOrdersPerYear
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

テーブルの詳細（主キー、インデックス、外部キーなど）を指定する必要がある場合は、アプローチ1を使用します。

より一時的なものには、アプローチ2を使用します。(ここでは通常 #SalesOrdersPerYear のような一時的なテーブルを使用します)。

いずれにせよ、これでデータはテーブルに保存され、再び使用できるようになりました。

テンポラリテーブルを使用する

-- Check for existence and drop first to avoid errors if it already exists.
if OBJECT_ID('tempdb..#SalesOrdersPerYear') is not null
    drop table #SalesOrdersPerYear

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
select  SalesPersonID, BaseSalary AS TotalSales  
into #SalesOrdersPerYear
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

また、テーブル変数として定義することも可能で、これは2つのアプローチを少し掛け合わせたようなものです。

declare @SalesOrdersPerYear table
( SalesPersonID int, BaseSalary float)
;

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
insert into @SalesOrdersPerYear  
SELECT SalesPersonID, BaseSalary AS TotalSales  
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;  

このオプションはこのバッチにのみ適用され、他の変数と同様に、削除する必要はありません。