CODEWITHSUNDEEP
sql-serverswitch-statement
gstackoverflow
gstackoverflow

Reputation: 37106

SQL Server: using case while insert into

I have the following sql:

;WITH DatesCTE AS 
(
   SELECT CAST('2016-01-01' AS DATE) AS [workingDays]

   UNION ALL

   SELECT DATEADD(DAY, 1, workingdays)
   FROM DatesCTE
   WHERE DATEADD(DAY, 1, workingdays) < '2017-01-01'
)
insert into dbo.tWorkingDays
     (date,
     CASE ((DATEPART(dw, workingDays) + @@DATEFIRST) % 7) NOT IN (0, 1) when TRUE THEN 1,
        ELSE 0
    END
    )
SELECT *
FROM DatesCTE
WHERE ((DATEPART(dw, workingDays) + @@DATEFIRST) % 7) NOT IN (0, 1)
OPTION (MAXRECURSION 366)

and following table:

CREATE TABLE [dbo].[tWorkingDays]
(
    [date] [date] NOT NULL,
    [is_business_day][bit] NOT NULL DEFAULT 1,

    CONSTRAINT [PK_tWorkingDays] 
       PRIMARY KEY CLUSTERED ([date] ASC)
) ON [PRIMARY]

When I try to execute request I see following:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'CASE'.

What did I do wrong ?

Upvotes: 1

Views: 965

Answers (2)

Devart
Devart

Reputation: 122032

   ;WITH DatesCTE AS (
       SELECT CAST('20160101' AS DATE) AS [workingDays]
       UNION ALL

       SELECT DATEADD(DAY, 1, workingdays)
       FROM DatesCTE
       WHERE DATEADD(DAY, 1, workingdays) < '2017-01-01'
    )
    INSERT INTO dbo.tWorkingDays (date, is_business_day)
    SELECT *, CASE WHEN (DATEPART(dw, workingDays) + @@DATEFIRST) % 7 NOT IN (0, 1) THEN 1 ELSE 0 END
    FROM DatesCTE
    --WHERE (DATEPART(dw, workingDays) + @@DATEFIRST) % 7 NOT IN (0, 1)
    OPTION (MAXRECURSION 366)

Upvotes: 1

cf_en
cf_en

Reputation: 1661

Looks like you got the field list (the fields you're inserting into) mixed up with the select list (the values you want to insert). It should look like this:

;WITH DatesCTE
 AS (

   SELECT CAST('2016-01-01' AS DATE) AS [workingDays]
   UNION ALL

   SELECT DATEADD(DAY, 1, workingdays)
   FROM DatesCTE
   WHERE DATEADD(DAY, 1, workingdays) < '2017-01-01'

)
insert into dbo.tWorkingDays
     (date, is_business_day)
SELECT workingDays,
     CASE WHEN ((DATEPART(dw, workingDays) + @@DATEFIRST) % 7) NOT IN (0, 1) THEN 1 ELSE 0
    END
FROM DatesCTE
WHERE ((DATEPART(dw, workingDays) + @@DATEFIRST) % 7) NOT IN (0, 1)
OPTION (MAXRECURSION 366)

You probably don't want the WHERE clause and the CASE in the same query, but the quoted code should at least resolve the syntax error.

Upvotes: 1

Related Questions