[tsql] Combining INSERT INTO and WITH/CTE

I have a very complex CTE and I would like to insert the result into a physical table.

Is the following valid?

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

I am thinking of using a function to create this CTE which will allow me to reuse. Any thoughts?

The WITH clause for Common Table Expressions go at the top.

Wrapping every insert in a CTE has the benefit of visually segregating the query logic from the column mapping.

Spot the mistake:

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_

Same mistake:

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

A few lines of boilerplate make it extremely easy to verify the code inserts the right number of columns in the right order, even with a very large number of columns. Your future self will thank you later.

Yep:

WITH tab (
  bla bla
)

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

SELECT * FROM tab

Note that this is for SQL Server, which supports multiple CTEs:

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

Teradata allows only one CTE and the syntax is as your example.