SQL Add column with conditional values from another column-CodePudding

I want to insert multiple columns that will be numerical values with multiple conditional statements into a temp table, but I'm unsure about the sequence of steps. (I'm using a temp table to test my query before actually editing the real table)

select * into #tempt from [dbo].[MainTable];

alter table #tempt 
  add [IsColumn] numeric,
      [IsTest] numeric,
      [ThisColumn] numeric;

select #tempt.Called,
  case
    when Called in ('Missed', 'No Answer', 'Voicemail', 'Disconnected') then 1 else 0
  end as IsColumn
from #tempt;

The case when works, but it's not actually inserting into the #tempt. I have the conditionals for the other new columns but I'm not sure how to get this to work correctly. All of the other conditionals are the same format - if a specific column says "__", then enter a 1 in the new column, else, enter 0.

CodePudding user response：

If you mean that you don't understand why your SELECT statement is not updating your table, it's because you need to use an UPDATE statement.

What I think you want is:

UPDATE #tempt
  SET IsColumn = case
    when Called in ('Missed', 'No Answer', 'Voicemail', 'Disconnected') then 1 else 0
  end
;

CodePudding user response：

select does not change the data in tables. This looks like an use case for a temporary view but unfortunately SQL Server does not support temporary views.
So instead of first creating and filling #tempt with the structure and data from [dbo].[MainTable] then adding three columns to it and then filling them (using update) with calculated values better add and calculate them within the select list of the initial query which creates #tempt. You will have it all done in one go. Here is my suggestion:

select *,
  case when Called in ('Missed', 'No Answer', 'Voicemail', 'Disconnected') then 1 else 0 end as IsColumn,
  case when left(specific_column_a, 2) = '__' then 1 else 0 end as IsTest, 
  case when left(specific_column_b, 2) = '__' then 1 else 0 end as ThisColumn 
into #tempt
from [dbo].[MainTable];

You may see this SO thread too.