Insert Statement Query

Question

I have 882 rows in my Table the columns are (StartDate, EndDate, Capacity, PitchType_Skey, Site_Skey) and these rows are based on 2010 dates, what I would like to do is insert another 882 rows for the year 2011, but I cannot figure how to do this, can you help. I have started my Insert Statement:

INSERT INTO Cap1 (StartDate, EndDate, Capacity, PitchType_Skey, Site_Skey)

SELECT
Add 1 YEAR to StartDate,
Add 1 YEAR to EndDate,
Capacity,
Pitch_Type_Skey,
Site_Skey
FROM Cap1
WHERE YEAR StartDate = 2010

Thanks in Advance

Wayne

Oh and whilst there is a `Year()` function to get the year part from a datetime, it's more performant to a range check with `BETWEEN`. — Bridge, Feb 14 '13 at 08:33

score 3 · Answer 1 · edited May 23 '17 at 10:32

3

Assuming that the StartDate and EndDate are of data type date or date time, then you can use the DATEADD function to add increment the year by 1 like this:

INSERT INTO Cap1 (StartDate, EndDate, Capacity, PitchType_Skey, Site_Skey)

SELECT
  DATEADD(yy, 1, StartDate),
  DATEADD(yy, 1, EndDate),
  Capacity,
  Pitch_Type_Skey,
  Site_Skey
FROM Cap1
WHERE YEAR(StartDate) = 2010;

Note that: As noted by @Bridge, @brykneval the YEAR function is not sargable, therefore the the SQL Server optimizer can't use an index on that field inside the YEAR function. For better performance, use the predicate BETWEEN, or <=, >= instead.

edited May 23 '17 at 10:32

Community

1
1

answered Feb 14 '13 at 08:32

Mahmoud Gamal

78,257
17
139
164

1

`WHERE` clause needs brackets for the year function! :-) – Bridge Feb 14 '13 at 08:34
@Bridge Sorry, I just copied from the OP question. Thanks fixed now. – Mahmoud Gamal Feb 14 '13 at 08:36
Hi Mahmoud Thanks for that so quickly but I get the following error Msg 4145, Level 15, State 1, Line 11 An expression of non-boolean type specified in a context where a condition is expected, near 'StartDate'. – wafw1971 Feb 14 '13 at 08:37
@wafw1971 - Sorry I missed that. Use `Year(startDate)` see my edit. – Mahmoud Gamal Feb 14 '13 at 08:37
1

@wafw1971 You probably took Mahmoud's answer before the edit - try again with the fixed version. – Bridge Feb 14 '13 at 08:38
Isn't using YEAR(StartDate) = 2010 Non-Sargable ?? – Sandip Bantawa Feb 14 '13 at 08:41
@brykneval - Yes it is not. I added this as a note. Thanks for the tip :) – Mahmoud Gamal Feb 14 '13 at 08:50

score 0 · Answer 2 · answered Feb 14 '13 at 08:34

INSERT INTO Cap1 (StartDate, EndDate, Capacity, PitchType_Skey, Site_Skey)

                 SELECT
                     DateAdd(yy,1,StartDate),
                     DateAdd(yy, 1,EndDate),
                     Capacity,
                     Pitch_Type_Skey,
                     Site_Skey
                     FROM Cap1
                      WHERE YEAR(StartDate) = 2010

Insert Statement Query

2 Answers2