Need help with loop, variable and insert

[mstrpup]

SQL 2000

I have never done loops or variables in SQL, but I think I need to for my current problem. Any help on the syntax would be greatly appreciated!

I need to populate a table with ranges of numbers. My table will have only 2 columns… “from” and “to” values, and should have no gaps between the rows.

Final Table example…
From To
120015 120019
120020 180039
180040 200029
200030 999999

The raw data contains only the “from” value…
From
120015
120020
180040
200030

My thought is that I should be able to loop through this table in descending order, calculate and save the “From -1” value and update it into the “To” value of the next row.


So, here is the logic I think I need. How would I do this in SQL? I won’t know how many rows I have ahead of time.

Set Variable = 999999 (for highest value row)
While there are rows…
Read Raw Data file
Insert into final table (From, Variable)
Read Next Row


Data would look like this in the loop…
First pass
From = 200030
Insert into Final Table (From, Variable) (200030, 999999)
Set Variable = From -1 (200029)

Second pass
From = 180040
Insert into Final Table (From, Variable) (180040, 200029)
Set Variable = From -1 (180039)

Third pass
From = 120020
Insert into Final Table (From, Variable) (120020, 180039)
Set Variable = From -1 (120019)

Etc.


So my final table (descending, as input) would be
200030, 999999
180040, 200029
120020, 180039
120015, 120019



Thank you!
~Toni

 

[markros]

Too bad it's SQL 2000 - the solution would be easier in SQL Server 2005 and up.

Anyway,

declare @t table (ID int identity(1,1) primary key, [From] int)

insert into @t (From) select [FROM] from myTable

select T1.[From] as [From], T2.[From]-1 as [To] from @t T1 left join @t T2 on T1.ID + 1 = T2.ID

From the top of my head - not tested

 

[snitin78]

See if this meets your purpose:

declare @sourcetable table (frrom int)
declare @desttable table (frrom int, too int)

insert into @sourcetable values (120015)
insert into @sourcetable values (120020)
insert into @sourcetable values (180040)
insert into @sourcetable values (200030)

declare @dataCursor cursor
declare @prevFromValue int, @fromValue int, @toValue int, @loopVariable int = 0

set @dataCursor = cursor for select * from @sourcetable order by frrom desc

open @dataCursor

fetch next from @dataCursor into @fromValue
while(@@fetch_status = 0)
begin
	
	if(@loopVariable = 0)
	begin
		set @toValue = 999999
		set @prevFromValue = @fromValue
	end
	else
	begin
		set @toValue = @prevFromValue - 1
		set @prevFromValue = @fromValue
	end
	
	insert into @desttable values (@fromValue, @toValue)
	
	set @loopVariable = @loopVariable + 1
	fetch next from @dataCursor into @fromValue
end

select * from @desttable

Nitin

 

[markros]

In my code I forgot to add ORDER BY in Insert command. It should be

insert into @t select [From] From myTable order by [From]

Also, it's better to not use [From] as a field name.

 

[mstrpup]

Thank you both! I'll give those a try and see if I can get it to work.

 

[JeffModen]

 SELECT [From], 
        (SELECT ISNULL(MIN([From])-1,999999) AS [To] FROM #yourtable o2 WHERE o2.[From] > o1.[From])
   FROM yourtable o1

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

Nah... forget what I posted... I didn't realize that it makes a performance crushing Triangular Join. Sorry folks.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[markros]

What do you think about my suggestion?

 

[JeffModen]

In SQL Server 2000, that would probably be the way I'd go except that I'd probably use a Temp Table and SELECT/INTO if there were a decent amount of data.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[mstrpup]

Thanks for trying Jeff!

markros, I couldn't get your join to work, it returns no data. I had been trying something similar before I came here for help.

I was able to get snitin78's code to work, so I think I'm set.

Thanks so much for all your help guys! You rock!

Toni

 

[markros]

Very strange - I don't see a reason why it would not work.

 

[JeffModen]

This looks very much the same but is quite different and quite fast (especially if you have an index on the column). No triangular join here...

 SELECT [From], 
        ISNULL((SELECT TOP 1 o2.[From]-1 AS [To] FROM yourtable o2 WHERE o2.[From] > o1.[From] ORDER BY o2.[From]) ,999999)
   FROM #yourtable o1

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row