I find it extremely annoying that this does not work:

DECLARE @Weight FLOAT = 0

,@STDate DATETIME

,@EDate DATETIME

SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE

;WITH Tally AS (

SELECT TOP (1+DATEDIFF(day, @STDate, @EDate)) n=number-1

FROM [master].dbo.spt_values Tally

WHERE [Type] = 'P' AND number BETWEEN 1 AND 100)

MERGE #SAMPLETABLE t

USING Tally s

ON t.DATECOL = DATEADD(day, n, @STDate)

WHEN MATCHED THEN

UPDATE SET @Weight = WEIGHTS

WHEN NOT MATCHED THEN

INSERT (DATECOL, WEIGHTS)

VALUES (DATEADD(day, n, @STDate), @Weight);

SELECT *

FROM #SAMPLETABLE

ORDER BY DATECOL

DROP TABLE #SAMPLETABLE

When BOL (http://technet.microsoft.com/en-us/library/bb510625.aspx) says that you should be able to SET assign to a local variable.