Insert into from Cte

Combining INSERT INTO and WITH/CTE

You need to put the CTE first and then combine the INSERT INTO with your select statement. Also, the "AS" keyword following the CTE's name is not optional:

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

Please note that the code assumes that the CTE will return exactly four fields and that those fields are matching in order and type with those specified in the INSERT statement.
If that is not the case, just replace the "SELECT *" with a specific select of the fields that you require.

As for your question on using a function, I would say "it depends". If you are putting the data in a table just because of performance reasons, and the speed is acceptable when using it through a function, then I'd consider function to be an option.
On the other hand, if you need to use the result of the CTE in several different queries, and speed is already an issue, I'd go for a table (either regular, or temp).

WITH common_table_expression (Transact-SQL)

Insert into table from CTE

Try this syntax:

INSERT INTO tmp( tmp_id )
  WITH cte AS (
    SELECT 1 AS tmp_id FROM dual
  )
  SELECT tmp_id
  FROM cte;

https://dev.mysql.com/doc/refman/8.0/en/with.html

TSQL Insert into with CTE, how to do it?

The correct syntax is that need to put INSERT INTO in front of SELECT

;with HRdataCTE as 
(
    Select 
    BusinessEntityID,
    HireDate,
    Month(HireDate) as HireMth,
    Datename(M,HireDate) as HireMthName,
    DATEPART(M,HireDate) as HireMth2,
    YEAR(HireDate) as HireYear
    from HumanResources.Employee
)
Insert into HRanalysis(
    HireYear,
    HireMth,
    HireMthName,
    MthQuantity,
    YearQuantity,
    YearMhtCumulatively)
select distinct
    HireYear,
    HireMth,
    HireMthName,
    COUNT(*) over (partition by HireYear, HireMthName) as MthQuantity,
    COUNT(*) over (partition by HireYear) as YearQuantity,
    COUNT(*) over (partition by HireYear order by HireMth) as YearMhtCumulatively
from HRdataCTE

Insert into from CTE

This is the syntax to insert into a table from a CTE:

-- CREATE TABLE tmp ( tmp_id NUMBER(10) );

INSERT INTO tmp( tmp_id )
  WITH cte AS (
    SELECT 1 AS tmp_id FROM dual
  )
  SELECT tmp_id
  FROM cte;

Does Trino (formerly Presto) INSERT work with CTEs?

I had the order of the CTE and INSERT backwards.

This works:

INSERT INTO my_destination_table

with my_CTE as 
(SELECT a,b,c
FROM my_source_table
WHERE <some conditions to apply>)

SELECT a, b, c
FROM my_CTE;

See the previous Stack Exchange Q&A in my comment above.

Using CTE function for insert into statement

Try this:

create table demo (col1 int);

insert /*+ with_plsql */ into demo (col1)
with
    function add_one(p_id number) return number
    as
    begin
        return p_id + 1; 
    end;
select add_one(rownum) 
from dual
/

https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

Some tools may not handle this syntax. It worked for me in SQL*Plus 12.1 (when terminated by the slash character, as for PL/SQL code) but failed in PL/SQL Developer 12.0.7 (Oracle 12.1).

Insert query using CTE in SQL Server

Declare the cte first, then the insert from select list from cte:

;WITH article_type_list AS
(
    SELECT 
       art_typ_child_index, art_typ_parent_index
    FROM
       [autoFIE2].[dbo].[tbl_article_type_parent_child]  
    WHERE 
       art_typ_parent_index IS NULL 
    UNION ALL
    SELECT 
       a.art_typ_child_index, a.art_typ_parent_index
    FROM
       [autoFIE2].[dbo].[tbl_article_type_parent_child] A 
    INNER JOIN 
       article_type_list as AL ON a.art_typ_parent_index = al.art_typ_child_index
    WHERE 
       a.art_typ_parent_index IS NOT NULL
)
INSERT INTO [autoFIE2].[dbo].[tbl_article_type_parent_child] 
([art_typ_parent_index], [art_typ_child_index])
SELECT * FROM article_type_list;

---

Related Topics