how to create this table - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

how to create this table

Author

Topic

spikeic
Starting Member

2 Posts

Posted - 2004-08-05 : 12:44:07

I'm looking to create a simple table containing the following:

Start Stop
0 5
5 10
10 15
15 20

etc...

Is there some kind of looping routine to perform n=n+5? I also need some statement to end the loop. For instance, I want to be able to end this table at 1000 and 1005.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-05 : 12:56:49

declare @inc int
set @inc = 0
declare @inc1 int
set @inc1 = 5

UPDATE Table1
SET @inc = Start = @inc + 5,
@inc1 = Stop = @inc1 + 5

it could probably be done with just one variable...

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-05 : 12:59:26

Declare @myTable1 table (n int)
Insert Into @myTable1
Select 0 Union All Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All
Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9

Insert Into @myTable1 Select n=n+(Select max(n)+1 From @myTable1) From @myTable1
Insert Into @myTable1 Select n=n+(Select max(n)+1 From @myTable1) From @myTable1
Insert Into @myTable1 Select n=n+(Select max(n)+1 From @myTable1) From @myTable1
Insert Into @myTable1 Select n=n+(Select max(n)+1 From @myTable1) From @myTable1
Insert Into @myTable1 Select n=n+(Select max(n)+1 From @myTable1) From @myTable1

Select * From @myTable1

Declare @myTable2 table (start int, stop int)

Insert Into @myTable2
Select
Start = 5*n,
Stop = 5*(n+1)
From @myTable1

Select * From @myTable2 Where Start<=1000

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-05 : 13:00:47

Realistically, I have a sequence routine that generates a seq from a A to B stepping by C

so normally I would do

Select
Start = number,
Stop = number + 5
From dbo.getSequence(0,1000,5)

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-05 : 13:03:31

with one variable:

declare @inc int
set @inc = -5 -- if you want to start with 0, it's 0 if you want to start with 5

UPDATE #temp
SET @inc = Start = (@inc + 5),
Stop = (@inc + 5)

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources