Combinaison D'INSERT dans et avec/CTE

J'ai un CTE très complexe et je voudrais insérer le résultat dans une table physique.

Est-ce que ce qui suit est valide?

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

Je pense utiliser une fonction pour créer ce CTE qui me permettra de réutiliser. Toutes les pensées?

129
demandé sur Kirk Broadhurst 2010-07-22 09:48:43

4 réponses

Vous devez d'abord mettre le CTE, puis combiner L'INSERT INTO avec votre instruction select. En outre, le mot-clé" AS " suivant le nom du CTE n'est pas facultatif:

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

Veuillez noter que le code suppose que le CTE retournera exactement quatre champs et que ces champs correspondent dans l'ordre et le type avec ceux spécifiés dans L'instruction INSERT. Si ce n'est pas le cas, remplacez simplement le "SELECT *" par une sélection spécifique des champs dont vous avez besoin.

Quant à votre question en utilisant une fonction, je dirais "ça dépend". Si vous mettez les données dans une table juste pour des raisons de performance, et que la vitesse est acceptable lors de l'utilisation via une fonction, alors je considérerais la fonction comme une option. D'un autre côté, si vous avez besoin d'utiliser le résultat du CTE dans plusieurs requêtes différentes, et que la vitesse est déjà un problème, j'opterais pour une table (régulière ou temporaire).

Avec common_table_expression (Transact-SQL)

224
répondu Valentino Vranken 2012-05-24 11:38:46

Oui:

WITH tab (
  bla bla
)

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

SELECT * FROM tab

Notez que ceci est pour SQL Server, qui prend en charge plusieurs CTEs:

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

Teradata n'autorise qu'un seul CTE et la syntaxe est votre exemple.

15
répondu Cade Roux 2010-07-22 05:52:27

La clause WITH pour les Expressions de Table courantes va en haut.

L'encapsulation de chaque insert dans un CTE a l'avantage de séparer visuellement la logique de requête du mappage de colonne.

Repérer l'erreur:

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_

Même erreur:

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

Quelques lignes de passe-partout rendent extrêmement facile de vérifier que le code insère le bon nombre de colonnes dans le bon ordre, même avec un très grand nombre de colonnes. Votre futur moi vous remerciera tard.

14
répondu Anon 2015-05-08 20:49:11

Voici la syntaxe Google big query.

Insert Into la déclaration doit être avant 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
répondu Selcuk Akbas 2018-09-23 21:31:36