Update Temp table with a While Loop

_{Tuesday, September 15, 2009 4:27 PM}

Hi there,

This should be very simple I guess, what I'm trying to do here is just to update a column in a temp table with sequential numbers. starting from whatever until the EOF

Thanks,

Manny

_{Tuesday, September 15, 2009 5:23 PM ✅Answered}

it is not recommended to use while loop to update the table because set based operation is the strength of sql. As I read somewhere once, you don't tell SQL how to do it, you tell SQL what you want, and that's a great way of thinking about it.   

anyways if you want to know how to use a while loop here is the script :)

declare @id int, @c int
DECLARE @t TABLE(
id INT,
col CHAR(1), val int
);

set @id =0
set @c =100
INSERT INTO @t VALUES (1,'a', 0);
INSERT INTO @t VALUES (2,'b', 0);
INSERT INTO @t VALUES (3,'c', 0);
 
select top 1 @id = id from @t where val =0
while @id is not null
    begin
    update @t set val = @c where id =@id
    set @c = @c+1
    set @id = null
    select top 1 @id = id from @t where val =0

    end

select * from @t

_{Tuesday, September 15, 2009 4:43 PM}

Hi Manny,

Can you explain a bit more about what you are trying to accomplish?  I really do not see an need to update the temp table at all.  If you are using SQL Server 2005 and greater, you can use the row_number function to assign a sequential number to each row.  This is possible in prior versions of SQL also, but it is much easier to do in SQL 2005+. 

Row_Number in BOL - http://msdn.microsoft.com/en-us/library/ms186734.aspx

DECLARE @t TABLE(
id INT,
col CHAR(1)
);

INSERT INTO @t VALUES (1,'a');
INSERT INTO @t VALUES (2,'b');
INSERT INTO @t VALUES (3,'c');

SELECT *,ROW_NUMBER() OVER(ORDER BY col ASC) AS RowNum
FROM @t
ORDER BY RowNum

http://jahaines.blogspot.com/

_{Tuesday, September 15, 2009 7:47 PM}

Thanks Arif, that worked.

Thanks again.

Manny

_{Tuesday, September 15, 2009 8:43 PM}

The above solution is not a scalable solution.  Have you looked at the links I posted. Also in SQL 2000, you dont have to update the rows, you should create the sequence when the temp table is created.

USE [tempdb]
GO

--Start Create sample table
IF OBJECT_ID ('tempdb..Foo') IS NOT NULL
BEGIN
    DROP TABLE Foo
END
GO

CREATE TABLE Foo (
Startdate DATETIME NOT null,
Conversion DECIMAL(9,2)
)

ALTER TABLE [Foo]
ADD PRIMARY KEY CLUSTERED (StartDate ASC)
GO

INSERT INTO Foo VALUES ('2002/01/01',0.1);
INSERT INTO Foo VALUES ('2002/02/01',0.2);
INSERT INTO Foo VALUES ('2002/03/01',0.3);

--End Create sample table

--SQL 2005 Solution
SELECT 
    ROW_NUMBER() OVER (ORDER BY StartDate) AS RowNum,
    *
FROM dbo.Foo

--START SQL 2000 Solution
IF OBJECT_ID ('tempdb..#temp') IS NOT NULL
BEGIN
    DROP TABLE #temp
END
GO

CREATE TABLE #temp(
Seq INT IDENTITY(1,1),
StartDate DATETIME  NOT NULL,
Conversion DECIMAL(9,2)
)

--this key forces the sort order of your row sequencing
ALTER TABLE [#temp]
ADD PRIMARY KEY CLUSTERED (StartDate ASC)
GO

INSERT INTO [#temp] ([StartDate],[Conversion])
SELECT
    StartDate,
    [Conversion]
FROM [Foo]
ORDER BY [StartDate]

SELECT *
FROM #temp 
ORDER BY [StartDate]

http://jahaines.blogspot.com/