TSQL Insert into with CTE, how to do it?

Question

I'm struggling with the problem below.

I have written CTE where I calculate quantity of headcount in year,mth and cumulatively.

My code:

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
    )

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

If I wanna create a view in SQL it will be ok, but suppose I have a table like this:

create table HRanalysis (
    HireYear int,
    HireMth int,
    HireMthName varchar(20),
    MthQuantity int,
    YearQuantity int,
    YearMhtCumulatively int)

and I wanna insert my code from CTE to this table I have an error about semicolon (?) between insert into statement and CTE.

Insert into HRanalysis(
    HireYear,
    HireMth,
    HireMthName,
    MthQuantity,
    YearQuantity,
    YearMhtCumulatively)
    ;
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
    )

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

Error message: Msg 102, Level 15, State 1, Line 20 Incorrect syntax near ';'. It is this error between insert into statement and CTE

So How to put this CTE to a table HRanalysis? Please help me

Answer 1

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