Как создать таблицу с помощью предложения "С" в SQL

Question

Я пытаюсь создать постоянную таблицу с помощью предложения 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; 

Однако я получаю ошибку

Сообщение 319, уровень 15, состояние 1, строка 5 Неправильный синтаксис рядом с ключевым словом "с". Если этот оператор является общим табличным выражением, предложением 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;  
)

Кто-нибудь сможет дать некоторые рекомендации по этому вопросу?

Спасибо заранее!

Answer 1

Это недопустимый синтаксис для сервера sql. Вы можете создать таблицу, используя 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;  

Вы также можете определить ее как табличную переменную, которая представляет собой нечто среднее между подходами:

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;  

Эта опция будет сохраняться только в этом пакете и не нуждается в удалении - как и любая другая переменная.

Answer 2

Создание представления с помощью WITH и SELECT :

---

      CREATE VIEW  SCHEMA.DEMO
AS 
WITH STAGING AS ( 
   SELECT col1 as id,col2 as name from tbl1
   UNION ALL
   SELECT col1 as id,col3 as name from tbl2
) 
SELECT distinct id,name
FROM STAGING;

---

Answer 3

CREATE VIEW AS my_view
AS 
WITH my_tables_data AS ( 
   SELECT another_table.data1 AS some_value 
   FROM another_table
) 

SELECT * 
FROM some_data; 

CREATE TABLE my_table AS 
SELECT * FROM my_view;