Inserting a value from another table based on the difference of two other column values

Question

I'm trying to add a column value to my current insert/select statement but I'm not sure if I can use a CASE WHEN clause or if I need to do this another way.

Basically I have a phone number column in my main table. I need to insert a number into it from another table, but the other table has 2 different phone number columns depending on the call type. For instance:

CALLINGPARTYNO | FINALLYCALLEDPARTYNO | CALLTYPE
------------------------------------------------
1234567890        0987654321              1
0987654321        1234567890              2

So what I need to do is dictate which number gets put into my phoneNumber column in table 1. If call type is 1, I want to insert the number from the FINALLYCALLEDPARTYNO but if call type is 2, I want to insert the number from CALLINGPARTYNO.

Pseudo code if it helps:

Insert into table1(phoneNumber)
SELECT case when /*if calltype = 1, then select FINALLYCALLEDPARTYNO*/
    ELSE /*if calltype=2 then select CALLINGPARTYNO */ as phoneNumber

from table2;

Is there a way to do this with CASE or is there another way? Looking for the most ideal way to do this so that I can add it into a much larger insert statement.

Answer 1

You can use any syntactic constructs you can use in a select statement in an insert-select statement:

INSERT INTO table1(phoneNumber)
SELECT CASE calltype WHEN 1 THEN finallycalledpartyno
                     WHEN 2 THEN callingpartyno
       END
FROM   table2