increment number of fields in MSSQL

**jvst** · 05-08-2012, 2:03 PM

hello everyone,

this is part of the algorithm that i need to code using MS SQL

k = 1
C1 = generate counts from R1
repeat
k = k + 1
INSERT INTO R!k
SELECT p.Id, p.Item1, …, p.Itemk-1, q.Item
FROM Rk-1 AS p, TransactionTable as q
WHERE q.Id = p.Id AND
q.Item > p.Itemk-1
.
.
until Rk = {}

so i came up with this one:

create table r2 (salesno bigint, prodno1 bigint ,prodno2 bigint )
insert into r2
select p.salesNo, p.prodNo, q.prodNo
from salesLog as p, salesLog as q
where q.salesNo = p.salesNo and q.prodNo>p.prodNo

notice the fields prodno1 and prodno2 in the table structure..

this is part of a number of sql statements i need to run and put inside a loop. my problem is i want to automatically use this same code such that on the next loop, this sql statement is going to be:

create table r3 (salesno bigint, prodno1 bigint ,prodno2 bigint, prodno3 bigint)
insert into r3
select p.salesNo, p.prodNo1, p.prodNo2, q.prodNo
from l2 as p, salesLog as q
where q.salesNo = p.salesNo and q.prodNo>p.prodNo2

Note that in this "2nd loop"
- the table name is now r3 from r2 in the first SQL statement
- there are now prodno1, prodno2 and prodno3 instead of just prodno1 and prodno2
- in the WHERE clause, the p.prodNo becomes p.prodNo2

and so on and so forth.. so for the 3rd loop

- there's going to be r4
- there's going to be prodno4
- in the WHERE clause i will use p.prodNo3

what should i do if i use vb here? is there an array or something that i can use? note that i am not trying to alter the structure of the table here, i am creating different tables with similar structures.

Please help! thank you in advance.