Combinando INSERT INTO y WITH / CTE

Tengo un CTE muy complejo y me gustaría insertar el resultado en una tabla física.

Es el siguiente valido?

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

Estoy pensando en usar una función para crear este CTE que me permita reutilizarlo. ¿Alguna idea?

Primero debe poner el CTE y luego combinar el INSERT INTO con su instrucción seleccionada. Además, la palabra clave “AS” que sigue al nombre del CTE no es opcional:

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

Tenga en cuenta que el código asume que el CTE devolverá exactamente cuatro campos y que esos campos coinciden en orden y tipo con los especificados en la instrucción INSERT. Si ese no es el caso, simplemente reemplace “SELECT *” con una selección específica de los campos que necesita.

En cuanto a su pregunta sobre el uso de una función, yo diría “depende”. Si está colocando los datos en una tabla solo por razones de rendimiento, y la velocidad es aceptable cuando la usa a través de una función, entonces consideraría la función como una opción. Por otro lado, si necesita usar el resultado del CTE en varias consultas diferentes, y la velocidad ya es un problema, elegiría una tabla (regular o temporal).

CON common_table_expression (Transact-SQL)

Sí:

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

Tenga en cuenta que esto es para SQL Server, que es compatible con múltiples CTE:

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

Teradata permite solo un CTE y la syntax es tu ejemplo.

La cláusula WITH para Common Table Expressions va en la parte superior.

Envolver cada inserción en un CTE tiene la ventaja de segregar visualmente la lógica de consulta del mapeo de columnas.

Localizar el error:

 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_ 

Mismo error:

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

Algunas líneas de texto repetitivo hacen que sea extremadamente fácil verificar que el código inserte el número correcto de columnas en el orden correcto, incluso con una gran cantidad de columnas. Tu yo futuro te lo agradecerá más tarde.