SQL while loop with Temp Table

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.

Use a set based approach - no need to loop (from the little details):

UPDATE 
       r 
 SET 
       r.Contact = m.SiteID + r.Contact 
 FROM 
       table1 r 
 INNER JOIN 
       #mod_contact m 
 ON m.id=r.id

Your brain wants to do this:

while records
   update(i);  //update record i
   records = records + 1
end while

SQL is set based and allows you to take a whole bunch of records and update them in a single command. The beauty of this is you can use the WHERE clause to filter certain rows that are not needed.

As others have mentioned, learning how to do loops in SQL is generally a bad idea; however, since you're trying to understand how to do something, here's an example:

DECLARE @id int

SELECT @ID =1 

WHILE @ID <= (SELECT MAX(ID) FROM table_1)
-- while some condition is true, then do the following
--actions between the BEGIN and END

BEGIN

UPDATE table_1
SET contact = CAST(siteID as varchar(100)) + contact
WHERE table_1.CID = @ID

--increment the step variable so that the condition will eventually be false

SET @ID = @ID + 1

END

--do something else once the condition is satisfied
PRINT 'DONE!!  Don't try this in production code...'

Try this one:

-- DECLARE the cursor
DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR SELECT column1,column2 FROM table

-- 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 

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT  * 
    FROM    Table2 
    WHERE   column1 = @varId
    AND column2 = @varSiteId

    FETCH NEXT FROM CUR INTO @varId, @varSiteId
END

CLOSE CUR
DEALLOCATE CUR

need to create a temporary table and then up date the original table.

Why use a temporary table at all? Your CID column doesn't appear in the temporary table, so I don't see how you can successfully update the original table using SiteID, unless there is only one row where CID = 142 in which using a temp table is definitely overkill.

You can just do this:

UPDATE dbo.table1
   SET contact = SiteID + contact
 WHERE CID = 142;

Here's a related example which may help getting you to 'think in SQL':

UPDATE T
   SET A = B, B = A;

Assuming A and B are of the same type, this would successfully swap their values.