CODEWITHSUNDEEP
sqlsql-servert-sql
Travis
Travis

Reputation: 3193

SQL while loop with Temp Table

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.

Upvotes: 7

Views: 72950

Answers (5)

deni
deni

Reputation: 11

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

Upvotes: 1

JonH
JonH

Reputation: 33143

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.

Upvotes: 5

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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

Upvotes: 2

Christiaan Nieuwlaat
Christiaan Nieuwlaat

Reputation: 1359

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.

Upvotes: 7

Related Questions