CODEWITHSUNDEEP
sqlsql-server
AZE
AZE

Reputation: 47

Incorrect syntax near the keyword 'WITH' in SQL Server

I was able to find a lot of articles regarding this issue, but nothing helped. So I'm trying to insert data to the table: 

INSERT INTO [dbo].[TV.EIP_Year_Month] ([Number], [Data])
;WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber + 1 
    FROM months
    WHERE MonthNumber < 12
)
SELECT
    CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + '-' + 
        CAST(MonthNumber AS VARCHAR) + '-01'  AS Data
FROM 
    months

I get this error message:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ';'.

Any ideas how to fix this issue?

Upvotes: 2

Views: 3029

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Why you need to use CTE for just 12 months :

select datefromparts(year(getdate()), MonthNumber, 1) as [date]
from ( values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
     ) t (MonthNumber);

You can use DATEFROMPARTS() to convert date.

Upvotes: 1

Barry Piccinni
Barry Piccinni

Reputation: 1801

I would put the WITH statement before the INSERT like so:

WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
INSERT INTO [dbo].[TV.EIP_Year_Month]
  ([Number]
  ,[Data])
SELECT cast(datepart(year, GETDATE()) as varchar) + '-' + CAST(MonthNumber AS VARCHAR) + '-01'  as Data
FROM months

Assuming the rest of your statement works for you, arranging it as such should work.

Upvotes: 4

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

;WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
INSERT INTO [dbo].[TV.EIP_Year_Month]
      ([Number]
      ,[Data])
select ?[Number]?, cast(datepart(year, GETDATE()) as varchar) + '-' + CAST(MonthNumber AS VARCHAR) + '-01'  as Data
FROM months

Upvotes: 2

Related Questions