Looping SQL statement insert

Question

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'.

Have you tried removing the "values" keyword from the previous line? Insert/Select syntax does not require this keyword. — weaver
– weaver, Commented Apr 9, 2013 at 7:49

Sandip Bantawa · Accepted Answer · 2013-04-09 08:05:45Z

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;

edited Apr 9, 2013 at 8:05

Sandip Bantawa

2,9004 gold badges35 silver badges48 bronze badges

answered Apr 9, 2013 at 7:49

Azmi Kamis

9015 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Looping SQL statement insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related