For the dynamic SQL approach:

declare @sqlnvarchar(4000)

set @sql =

'

INSERT INTO '+

case

when @PeriodType = 'Monthly'then N'Monthly_Summary_Data'

when @PeriodType = 'Daily'then N'Daily_Summary_Data'

else null end

+' (Column_1, Column_2, etc.)

SELECT

Source_col_1,

Source_col_2

FROM

SourceTable

'

exec sp_executesql @sql