Looping SQL statement insert

问题:

I want to insert each time a line with the date + 1. It's a simple SQL loop. I'm using SSIS, so the StartDate and Enddate are variables.

Here is my code:

WITH View_Solidnet_Training AS
(
    SELECT CAST('2013-04-09' AS DATETIME) DateValue
    UNION ALL

    SELECT DateValue + 1
    FROM   View_Solidnet_Training
    WHERE  DateValue + 1 < '2013-04-11'
)
INSERT INTO OBJ_Availability
VALUES
  SELECT 34,
         DateValue + 1,
         'AM',
         2,
         'Test'
  FROM   View_Solidnet_Training;

Error message:

Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'select'.

回答1:

No need for VALUES in INSERT...SELECT statement.

Replace

INSERT INTO OBJ_Availability
VALUES
  SELECT 34,
         DateValue + 1,
         'AM',
         2,
         'Test'
  FROM   View_Solidnet_Training;

with

INSERT INTO OBJ_Availability
SELECT 34,
       DateValue + 1,
       'AM',
       2,
       'Test'
FROM   View_Solidnet_Training;