Объединение INSERT INTO и WITH/CTE

У меня очень сложный CTE, и я хотел бы вставить результат в физическую таблицу.

Допустимо ли следующее:

INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos 
(
    BatchID,
    AccountNo,
    APartyNo,
    SourceRowID
)       
WITH tab (
  -- some query
)    
SELECT * FROM tab

Я думаю об использовании функции для создания этого CTE, которая позволит мне повторно использовать. Любые мысли?

Ответ 1

Вам нужно сначала поставить CTE, а затем объединить INSERT INTO с вашим предложением select. Кроме того, ключевое слово "AS", следующее за именем CTE, необязательно:

WITH tab AS (
    bla bla
)
INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (
BatchID,
AccountNo,
APartyNo,
SourceRowID
)  
SELECT * FROM tab

Обратите внимание, что код предполагает, что CTE вернет ровно четыре поля и что эти поля соответствуют по порядку и введите те, которые указаны в инструкции INSERT. Если это не так, просто замените "SELECT *" на конкретный выбор полей, которые вам нужны.

Что касается вашего вопроса об использовании функции, я бы сказал "это зависит". Если вы помещаете данные в таблицу только по соображениям производительности, и скорость приемлема при использовании ее через функцию, я бы рассматривал функцию как опцию. С другой стороны, если вам нужно использовать результат CTE в нескольких разных запросах, а скорость уже является проблемой, я бы пошел за таблицей (регулярной или временной).

WITH common_table_expression (Transact-SQL)

Ответ 2

Да:

WITH tab (
  bla bla
)

INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (  BatchID,                                                        AccountNo,
APartyNo,
SourceRowID)    

SELECT * FROM tab

Обратите внимание, что это для SQL Server, который поддерживает несколько CTE:

WITH x AS (), y AS () INSERT INTO z (a, b, c) SELECT a, b, c FROM y

Teradata позволяет использовать только один CTE, а синтаксис - как ваш пример.

Ответ 3

Предложение WITH для общих выражений таблицы находится вверху.

Обтекание каждой вставки в CTE позволяет визуально отделить логику запроса от сопоставления столбцов.

Определите ошибку:

WITH _INSERT_ AS (
  SELECT
    [BatchID]      = blah
   ,[APartyNo]     = blahblah
   ,[SourceRowID]  = blahblahblah
  FROM Table1 AS t1
)
INSERT Table2
      ([BatchID], [SourceRowID], [APartyNo])
SELECT [BatchID], [APartyNo], [SourceRowID]   
FROM _INSERT_

То же самое:

INSERT Table2 (
  [BatchID]
 ,[SourceRowID]
 ,[APartyNo]
)
SELECT
  [BatchID]      = blah
 ,[APartyNo]     = blahblah
 ,[SourceRowID]  = blahblahblah
FROM Table1 AS t1

Несколько строк шаблона позволяют очень просто проверить, что код вставляет правильное количество столбцов в правильном порядке, даже с очень большим количеством столбцов. Ваше будущее "я" вас поблагодарит позже.