Testing condition before inserting records SQL server

Question

Testing condition before inserting records SQL server

gregoryddrew

Grasshopper

Points: 18
More actions
May 14, 2018 at 12:39 pm

#344554

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

So.... what are you planning to do with the February inserts after inserting the January data? You may well end up with a LOT more rows than you need. That table appears to be a summary table, and you don't generally insert just January data in it. You generally insert all the necessary rows with NULL values for ell the monthly amounts, and then as each month occurs, you use an UPDATE to fill in the values. Dynamic SQL is probably the only way to do that consistently, but the possibly better alternative is to TRUNCATE the table and re-load it every month with all it's data, which will grow each month, and any month that''s not yet occurred will get either 0's or NULLs, depending on your exact query.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 2

To add to what Steve said about summary tables...those are often more of a display issue. They generally get their data from a more normalized table, where you would have something along the lines of month and amount. And then you pivot the data in a query to create a report with a summary table. In those cases, you are entering the month and the amount and that's it.

Sue

frederico_fonseca SSCoach Points: 16157 More actions · Answer 3

Take in consideration what Steve and Sue said and look at better/correct ways of doing your process.

However just as a possible rewrite of what you have you can try

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 , case when @month = 1 then month_amt else 0 end AS jan_amt , case when @month = 2 then month_amt else 0 end AS feb_amt , case when @month = 3 then month_amt else 0 end AS mar_amt .... FROM #actuals;