0

I'm sorry if this has been posted before, but I can't seem to find answers. I want to add integers from a certain start point to a certain end point to a temp table.

Let's say I have a table of vehicles. Each vehicle has a from and to year. For example, a Ford Mustang GT might have the same body style from 2006 to 2014.

So, I create the table

create table #TempYears
    (
        intYearID int
)

I can get the min and max years

SELECT min(yearFrom) As minYear,
       max(yearTo) AS MaxYear
FROM vehicle

But I'm not sure how to put the two together.

Thank you

Jack Pilowsky
  • 2,275
  • 5
  • 28
  • 38
  • You could post some sample data and the desired result. As it is, this question isn't clear enough to be answered correctly – Lamak Sep 20 '16 at 20:12
  • 3
    Possible duplicate of [how to generate a range of numbers between two numbers in sql server](http://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers-in-sql-server) – JNevill Sep 20 '16 at 20:13
  • Its a little different. I'm trying to put the results into a temp table – Jack Pilowsky Sep 20 '16 at 20:26
  • Use a recursive CTE with a `union all` and a where clause matching your year range. – ajeh Sep 20 '16 at 20:26
  • Besides the question you find as *possible duplicate* link you might read this related question: http://stackoverflow.com/q/39595634/5089204 – Shnugo Sep 20 '16 at 20:31

1 Answers1

1

You just need to insert FROM the cte used in JNevill's comment....

DECLARE @startnum INT
DECLARE @endnum INT
SET @startnum = (select min(yearFrom) from vehicle)
SET @endnum = (select max(yearFrom) from vehicle)

;WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
INSERT INTO #TempYears SELECT * FROM gen
SELECT * FROM #TempYears
S3S
  • 24,809
  • 5
  • 26
  • 45