INSERT SELECT WITH CASE STATEMENT

[747576]

Hi there,
I keep getting the following syntax error when I run the following SQL, what is the correct syntax. I am trying to use a CASE statement with an INSERT SELECT.
Thank you

Incorrect syntax near the keyword 'IS'.

--------------------------------------------------------
BEGIN TRAN
INSERT INTO TABLE1 (COLUMN1,COLUMN2,COLUMN3,COLUMN4)
SELECT VALUE1,
'VALUE2' = CASE type
WHEN VALUE2 IS NULL THEN GETDATE()
ELSE MAX(VALUE2)
END,
, VALUE3
, VALUE4
FROM TABLE2 T2 INNER JOIN TABLE3 T3 ON T2.VALUE = T3.VALUE
GROUP BY VALUE1, VALUE3, VALUE4
ORDER BY VALUE1
ROLLBACK TRAN

 

[maheswarkv]

I think the Insert syntax is wrong...you should use: Insert into tablename values(..). I think u forgot the Values keyword.

Thankyou.

 

[JarlH]

The VALUES keyword is only used when inserting one single row with values. So this is OK.

The ORDER BY clause makes no sense, and isn't allowed.

And why are you trying to assign a values (the CASE expression) to a literal?

Rewrite to:

INSERT INTO TABLE1 (COLUMN1,COLUMN2,COLUMN3,COLUMN4)
SELECT VALUE1,
       CASE 
         WHEN VALUE2 IS NULL THEN GETDATE()
         ELSE MAX(VALUE2)
       END,
       VALUE3,
       VALUE4
FROM TABLE2 T2 INNER JOIN TABLE3 T3 ON T2."VALUE" = T3."VALUE"
GROUP BY VALUE1, VALUE3, VALUE4

Note that VALUE is a reserved word. Rename or double quote ("VALUE").

 

[r937]

INSERT
  INTO TABLE1 
     ( COLUMN1
     , COLUMN2
     , COLUMN3
     , COLUMN4 )
SELECT VALUE1
     , [b]MAX(COALESCE(VALUE2,GETDATE())[/b]
     , VALUE3
     , VALUE4
  FROM TABLE2 T2 
INNER 
  JOIN TABLE3 T3 
    ON T3.VALUE = T2.VALUE
GROUP 
    BY VALUE1
     , VALUE3
     , VALUE4

r937.com | rudy.ca