lundi 14 mai 2018

Testing condition before inserting records SQL server

I have a stored procedure in SQL Server that inserts records for actual expenses into a table. When the procedure is invoked the month in question is specified as part of of a variable. For example:

exec dbo.upsert_actuals_load_01_load_data 4

When the code runs it's supposed to insert the records into the column that corresponds to the month. '1' inserts values into jan_amt, '2' inserts values into feb_amt, etc.

I have written this code:

IF @month = 1
   INSERT INTO #actuals_b
           ([forecast_yr_id]
           ,[entry_type]
           ,[unit_cd]
           ,[proj_nbr]
           ,[jan_amt]
           ,[feb_amt]
           ,[mar_amt]
           ...])
     SELECT forecast_yr_id
          , entry_type
          , unit_cd
          , proj_nbr
          , month_amt AS jan_amt
          , 0 AS feb_amt
          , 0 AS mar_amt
          ....
       FROM #actuals;

It seems inefficient to have to write the INSERT INTO statement for each IF @month = condition. Is there a better way to do this?

Aucun commentaire:

Enregistrer un commentaire