SQL while loop with Temp Table

Question

I need to create a temporary table and then update the original table. Creating the temporary table is not a problem.

create table #mod_contact
( 
    id INT IDENTITY NOT NULL PRIMARY KEY,
    SiteID INT,
    Contact1 varchar(25)
)

INSERT INTO #mod_contact (SiteID, Contact1)
select r.id, r.Contact  from dbo.table1 r where CID = 142
GO

Now I need to loop through the table and update r.contact = SiteID + r.contact

I have never used a while loop before and can't seem to make any examples I have seen work.

Answer 1

You can do this in multiple ways, but I think you're looking for a way using a cursor.

A cursor is sort of a pointer in a table, which when incremented points to the next record. ( it's more or less analogeous to a for-next loop )

to use a cursor you can do the following:

-- DECLARE the cursor
DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR SELECT id, siteId, contract FROM #mod_contract

-- DECLARE some variables to store the values in
DECLARE @varId int
DECLARE @varSiteId int
DECLARE @varContract varchar(25)

-- Use the cursor
OPEN CUR
FETCH NEXT FROM CUR INTO @varId, @varSiteId, @varContract

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE dbo.table1
SET contract = @varSiteId + @varContract -- It might not work due to the different types 
WHERE id = @varId 

FETCH NEXT FROM CUR INTO @varId, @varSiteId, @varContract
END

CLOSE CUR
DEALLOCATE CUR

It's not the most efficient way to get this done, but I think this is what you where looking for.

Hope it helps.