increment fields in MSSQL in a loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

increment fields in MSSQL in a loop

Author

Topic

jvst
Starting Member

12 Posts

Posted - 2012-05-08 : 13:43:43

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?

Please help! thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 16:05:32

sorry i really cant understand need of this requirement
Can you explain your business scenario using sample data? I certainly feel like overcomplicating the requirement here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources