4

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.

hermann
  • 6,237
  • 11
  • 46
  • 66

1 Answers1

6

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
Community
  • 1
  • 1
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Thanks, I cannot use COALESCE though. The reason is that depending on the column selected, some other value ( from a totally different table ) might go into the Manual_Value field. I wanted to implement the SELECT CASE method so I can explicitly decide what goes in the field. – hermann Nov 19 '12 at 10:57