Комбинирование вставки В и с/CTE

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

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

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

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

4 129
tsql insert common-table-expression

4 ответа:

вам нужно сначала поместить 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 обобщенное_табличное_выражение (Transact-SQL)

224

да:

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 и синтаксис в качестве примера.

15

The 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

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

14

вот большой синтаксис запроса google.

Insert Into оператор должен быть перед WITH

INSERT dataset.Warehouse (warehouse, state)
WITH w AS (
  SELECT ARRAY<STRUCT<warehouse string, state string>>
      [('warehouse #1', 'WA'),
       ('warehouse #2', 'CA'),
       ('warehouse #3', 'WA')] col
)
SELECT warehouse, state FROM w, UNNEST(w.col)
0