Repeating Record Sequence using SQL

Question

Hi,

This could easily be done using code, but I wondered if it could be done at the database level using SQL Server (2008).

I have a table similar to below:

CROP_ID   YEAR_   PRODUCTION   
1         1       0  
1         2       300  
1         3       500  
2         1       100
2         2       700

I want to be able to run a query to repeat this for n number of years, per crop type e.g.

CROP_ID  YEAR_  PRODUCTION  
1        1      0  
1        2      300  
1        3      500  
1        4      0  
1        5      300
1        6      500
etc.

I'm not sure of the best approach, I presume I would need a SP and pass in a year variable, and use a loop construct? However the exact syntax escapes me. Any help appreciated.

Update

Sorry for not providing all the information in my original post. The table will allow for multiple crop types, and for Produciton values to be updated so Case statements with fixed variables are not really suitable. Apologies for not being clearer.

Update

With the TVF answer I used the following modified SQL to select by CropType for 20 years.

select top 20 b.CROP_ID,  
YEAR_ = n.num * (select count() from MyBaseTable where CROP_ID = 3) + b.YEAR,  
b.PRODUCTION from MyBaseTable b, dbo.fnMakeNRows(20) n  
where CROP_ID = 3

Answer 1

A common trick to produce this kind of data without the need of a stored procedure is with the use of a table of constants. Because such a table can be of generic use, it can be created with say all the integers between 1 and 100 or even 1 and 1,000 depending on usage.

For exmaple

CREATE TABLE tblConstNums
( I INT )
INSERT INTO tblConstNums VALUES (1) 
INSERT INTO tblConstNums VALUES (2) 
INSERT INTO tblConstNums VALUES (3) 
INSERT INTO tblConstNums VALUES (4) 
INSERT INTO tblConstNums VALUES (5)
-- ...
INSERT INTO tblConstNums VALUES (1000)

The the solution can be written declaratively (without requiring Stored Procedure or more generally procedural statements:

SELECT CROP_ID,  YEAR_ * I,   PRODUCTION 
FROM myCropTable T
JOIN tblConstNums C on 1=1
WHERE I in (1, 2, 3)
order by CROP_ID,  YEAR_ * I,   PRODUCTION

Note that the table of constants may include several columns for commonly used cases. For example, and even though many of these can be expressed as mathematical expressions of numbers in a basic 0 to n sequence, one can have a column with only even number, another one with odd numbers, another one with multiples of 5 etc. Also if it small enough, no indexes are needed on a table of constants but these may become useful on a bigger on.

Answer 2

use this one:

WITH tn (n) as
(
    SELECT 0
  UNION ALL
    SELECT n+1
    FROM tn
  WHERE tn.n < 10
)
SELECT DISTINCT t.CROP_ID, t.YEAR_ + (3*tn.n), t.PRODUCTION
FROM table t, tn 

/*    
    WHERE tn.n < 10 ==> you will get 1 -> (10*3) + 3 = 33

*/

Answer 3

You can do this in standard SQL without creating a stored procedure or using temp tables. The example below will do this for 12 years. You can extend it out to any number of years:

insert into CropYield
(CropID, Year_, Production)
Select 1, a.a + (10 * b.a), 
    case (a.a + (10 * b.a)) % 3
        when 0 then 500
        when 1 then 0
        when 2 then 300
    end
from (Select 0 as a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) as a
cross join (Select 0 as a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) as b
where a.a + (10 * b.a) between 1 and 12

Answer 4

You could use a table-valued-function instead of a stored proc, which gives a little more flexibility for what you do with the result (as it can be selected from directly, inserted into another table, joined to other tables, etc).

You could also make this more generic by having a TVF generate N rows (with a number from 0 to N-1 on each row) and then use some simple expressions to generate the columns you need from this. I have found such a TVF to be useful in a variety of situations.

If you need to generate more complicated data than you can with 0..N-1 and simple expressions, then you should create a TVF dedicated to your specific needs.

The following example shows how a generic TVF could be used to generate the data you ask for:

create function fnMakeNRows (@num as integer)
returns @result table (num integer not null) as
begin
 if @num is null or @num = 0
 begin
  return
 end
 declare @n as integer
 set @n = 0
 while @n < @num
 begin
  insert into @result values (@n)
  set @n = @n + 1
 end
 return
end
go

select
 CROP_ID = 1,
 YEAR_ = num,
 PRODUCTION = case num % 3 when 0 then 0 when 1 then 300 else 500 end
from dbo.fnMakeNRows(100000)

You can also use this to duplicate rows in an existing table (which I think is more like what you want). For example, assuming base_table contains the three rows at the beginning of your question, you can turn the 3 rows into 60 rows using the following:

select
 b.CROP_ID,
 YEAR_ = n.num * (select count(*) from base_table) + b.YEAR_,
 b.PRODUCTION
from base_table b, dbo.fnMakeNRows(20) n

This (hopefully) shows the utility of a generic fnMakeNRows function.