Creating VB code to increment the number of fields in an MSSQL create table statement

hello everyone,

i have an SQL statement which i need to code in vb:

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? is there an array or something that i can use here?

thank you in advance.

Are you generating test data or something?

What I have done for some things a LITTLE like this before is use Excel. In column A put values 1, 2, 3... and then in column B use a CONCATENATE function.

=Concatenate("create table mytable",A1," (myfield",A1," int, ...")

Does that help at all?

i think i found what i have been looking for. thanks a lot!