I prefer
CTE[
^] rather than
WHILE
loop ;)
DECLARE @startDate DATETIME = '2014-10-30'
DECLARE @finishDate DATETIME = '2014-11-04'
;WITH MyDates AS
(
SELECT @startDate AS MyDate
UNION ALL
SELECT DATEADD(DD,1,MyDate) AS MyDate
FROM MyDates
WHERE DATEADD(DD,1,MyDate)<=@finishDate
)
INSERT INTO TableName (User_Vacation_ID, [Date])
SELECT 1 AS User_Vacation_ID, MyDate AS [Date]
FROM MyDates