Połączenie INSERT INTO i WITH / CTE

157

Mam bardzo złożony CTE i chciałbym wstawić wynik do fizycznej tabeli.

Czy poniższe informacje są ważne?

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

Myślę o użyciu funkcji do stworzenia tego CTE, która pozwoli mi ponownie użyć. jakieś pomysły?

tsql insert common-table-expression

— dcpartners
źródło

271

Musisz najpierw umieścić CTE, a następnie połączyć INSERT INTO z instrukcją select. Ponadto słowo kluczowe „AS” po nazwie CTE nie jest opcjonalne:

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

Należy pamiętać, że kod zakłada, że CTE zwróci dokładnie cztery pola i że te pola są zgodne w kolejności i typu z polami określonymi w instrukcji INSERT. Jeśli tak nie jest, po prostu zastąp „SELECT *” konkretnym wyborem wymaganych pól.

Jeśli chodzi o twoje pytanie dotyczące korzystania z funkcji, powiedziałbym, że „to zależy”. Jeśli umieszczasz dane w tabeli tylko ze względu na wydajność, a prędkość jest akceptowalna, gdy używasz jej przez funkcję, rozważę funkcję jako opcję. Z drugiej strony, jeśli chcesz użyć wyniku CTE w kilku różnych zapytaniach, a szybkość jest już problemem, wybrałbym tabelę (zwykłą lub tymczasową).

Z common_table_expression (Transact-SQL)

— Valentino Vranken
źródło

19

WITHKlauzula o potoczne tabela przejść na szczycie.

Zawijanie każdej wstawki w CTE ma tę zaletę, że wizualnie oddziela logikę zapytania od mapowania kolumn.

Znajdź błąd:

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_

Ten sam błąd:

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

Kilka wierszy schematu ułatwia weryfikację, czy kod wstawia odpowiednią liczbę kolumn we właściwej kolejności, nawet przy bardzo dużej liczbie kolumn. Twoje przyszłe ja podziękuje ci później.

— Zaraz
źródło

3

To jest świetne! Nagle nienawidzę stwierdzeń INSERT tak bardzo ...

— NReilingh

1

Jest to niezwykle przydatne. Dla każdego, kto przeoczył to przy pierwszym czytaniu, problem rozwiązuje ten problem polega na tym, że w instrukcji wstawiania odwzorowanie jest definiowane przez względną kolejność pól, w które mają być wstawione, i wartości, które mają być w nich wstawione, które są wymienione osobno. Jeśli piszesz je normalnie, niezwykle trudno jest sprawdzić przez oględziny, czy te dwie kolejności są takie same. CTE pozwala nazwać wartości nazwami kolumn, do których mają zostać wstawione, co oznacza, że można je bardzo ładnie wyrównać w dwóch wierszach.

— Tidorith

16

Tak:

WITH tab (
  bla bla
)

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

SELECT * FROM tab

Zwróć uwagę, że dotyczy to SQL Server, który obsługuje wiele CTE:

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

Teradata dopuszcza tylko jedno CTE, a składnia jest przykładowa.

— Cade Roux
źródło