CODEWITHSUNDEEP
sqlsql-servert-sql
Tom
Tom

Reputation: 2230

SQL INSERT (select) using CASE based on another columns value

I have an alarm trigger, and if that alarm trigger = 1, I want the value of another column to be NULL. For examples purposes, I'll create a simple table of what I'm trying to explain.

DECLARE @tmp TABLE ( ID INT, Value1 FLOAT, V1_Alarm BIT, Value2 FLOAT, V2_Alarm BIT)
INSERT INTO @tmp
SELECT (
ID,
CASE WHEN V1_Alarm = 1 THEN NULL ELSE Value1,
V1_Alarm,
CASE WHEN V2_Alarm = 1 THEN NULL ELSE Value2
) FROM SomeTable

Upvotes: 4

Views: 27262

Answers (1)

James Hill
James Hill

Reputation: 61812

You're not ending your case statements properly. Also, the parentheses are unnecessary and prevent multiple values from being passed. Here's your sample code modified:

DECLARE @tmp TABLE ( ID INT, Value1 FLOAT, V1_Alarm BIT, Value2 FLOAT, V2_Alarm BIT)
INSERT INTO @tmp
SELECT  ID,
        CASE WHEN V1_Alarm = 1 THEN 
            NULL
        ELSE
            Value1
        END,
        V1_Alarm,
        CASE WHEN V2_Alarm = 1 THEN
            NULL
        ELSE
            Value2
        END
FROM    SomeTable

Upvotes: 7

Related Questions