Insert query using CTE in SQL Server

Question

I want to insert into one table from another table using a CTE. I have tried to put semicolon before with, but it's not working.

This is my query :

INSERT INTO [autoFIE2].[dbo].[tbl_article_type_parent_child] ([art_typ_parent_index], [art_typ_child_index])
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)
SELECT * 
FROM article_type_list;

Error while executing this statement :-

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'.

Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

What should I do to insert this hierarchical data into another table. Any suggestion ?

You don't need to wrap the sub-query up with a CTE

Mostafa Armandi
– Mostafa Armandi

2015-01-21 05:50:05 +00:00
Commented Jan 21, 2015 at 5:50 — Mostafa Armandi
– Mostafa Armandi, Commented Jan 21, 2015 at 5:50

Mitch Wheat · Accepted Answer · 2015-01-21 07:04:31Z

7

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;

edited Jan 21, 2015 at 7:04

answered Jan 21, 2015 at 5:49

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert query using CTE in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related