INSERT INTO SELECT CASE

insert, select, select-case, sql, sql-server

Solution

Value_ID - ( Set_To_Value_ID goes here ) Manual_Value

Set_To_Variable_ID, Changed_In_SP OR Comment_Input goes here )

You can use the `COALESCE` expression like so:

INSERT INTO Processes 
SELECT 
  Set_To_Value_ID,
  COALESCE(Set_To_Variable_ID, Changed_In_SP, Comment_Input)
FROM Variables

Update: Well, since you couldn't use the `COALESCE` for this, here is the canonical way to do that using the `CASE` expression:

INSERT INTO Processes
SELECT
   Set_To_Value_ID,
   CASE 
     WHEN Set_To_Variable_ID IS NOT NULL THEN Set_To_Variable_ID 
     WHEN Changed_In_SP IS NOT NULL THEN  Changed_In_SP
     WHEN Comment_Input IS NOT NULL THEN  Comment_Input 
   END
FROM Variables

Problem

I have two tables, `Variables` and `Processes`. I want to implement a `INSERT INTO SELECT CASE`, but I've never done this before. Data goes from `Variables` to `Processes` always. But I don't know what columns. For example : `Variables` table has the columns below : `Variable_ID` `Set_To_Value_ID` `Set_To_Variable_ID` `Changed_In_SP` `Comment_Input` The first column is always copied to the `Processes` table. `Set_To_Value_ID`, `Set_To_Variable_ID`, `Changed_In_SP`, `Comment_Input` need a `SELECT CASE` because only one can have a value. three of these columns in a row will always be null. Whatever column is not null should be inserted in the `Processes` table : `Processes` table : `Process_ID` `Variable_ID` `Value_ID` - ( `Set_To_Value_ID` goes here ) `Manual_Value` - ( `Set_To_Variable_ID`, `Changed_In_SP` OR `Comment_Input` goes here ) `Variable_ID_To_Change` goes to `Variable_ID`. How to proceed? Thank you.

Original source