IF @PeriodType = 'Monthly'
BEGIN
  INSERT INTO Monthly_Summary_Data (Column_1, Column_2, etc.)
  SELECT (Value_1, Value_2, etc.)
    FROM SourceTable
END
IF @PeriodType = 'Daily'
BEGIN
  INSERT INTO Daily_Summary_Data (Column_1, Column_2, etc.)
  SELECT (Value_1, Value_2, etc.)
    FROM SourceTable
END

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

declare @sql		nvarchar(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