#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    7
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,429
    Rep Power
    4539
    You should review your table structure. In my opinion it's always a bad idea to alter table structure from code, particularly in a loop.

    You are probably looking for the traditional master/detail table structure, google for some table join tutorials at sites like www.w3schools.com
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Doug G
    You should review your table structure. In my opinion it's always a bad idea to alter table structure from code, particularly in a loop.

    You are probably looking for the traditional master/detail table structure, google for some table join tutorials at sites like
    thank you for your reply, i really appreciate it. let me just clarify a few things about my problem. i don't actually intend to alter the table structure.

    i would like to first create a table named r2, then on the third loop, automate the creation of another table named r3 with a little difference. so there are two tables created, the second with an additional field.

    on the third loop, it will create a third table r4, then with similar structure (just a difference of one more field).. and so on...
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Doug G
    You should review your table structure. In my opinion it's always a bad idea to alter table structure from code, particularly in a loop.

    You are probably looking for the traditional master/detail table structure, google for some table join tutorials at sites like
    here is the algorithm of my 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 = {}

IMN logo majestic logo threadwatch logo seochat tools logo