Looping Question!!..........

Hi all,
I have an initial parameter = 'TST0001'
I want to write an INSERT statement to automatically take the initial
parameter 'TST0001' and keep adding 1 to it until it get to 'TST9999'.
Now, my table should store data like these:
TST0001
TST0002
...
...
TST0010
TST0011
...
...
TST9999
Thanks,
Tom dYou can avoid looping by using a Numbers table for this sort of thing. See
the following articles:
http://www.bizdatasolutions.co_m/tsql/tblnumbers.asp
http://www.aspfaq.com/show.asp?id=2516
Here's an example:
CREATE TABLE foo (x VARCHAR(10) PRIMARY KEY)
INSERT INTO foo (x)
SELECT 'TST'+
RIGHT('0000'+CAST(N1.number*100+N2.number AS VARCHAR(4)),4)
FROM master.dbo.spt_values AS N1,
master.dbo.spt_values AS N2
WHERE N1.type = 'P'
AND N1.number BETWEEN 0 AND 99
AND N2.type = 'P'
AND N2.number BETWEEN 0 AND 99
I don't recommend you use this in any persistent code because spt_values
isn't documented. This is just to demonstrate what you can do with auxiliary
tables.
David Portas
SQL Server MVP
--