Loop Through Table & Add Values

I am writing a stored procedure to loop through a table, insert the values into a temporary table & then eventually I will loop through the temporary table & add the values.

So far I have the first loop written which will insert values into the temporary table.

I am getting this error...

There is already an object named '#testtable' in the database.

Here is my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

DECLARE @TestID int,
        @TestValue int,
        @sum int

CREATE TABLE #testtable (TestID int,
                    TestValue int)

DECLARE loop CURSOR FOR
SELECT TestValue
FROM tblTest
WHERE TestID > 1


OPEN loop
FETCH NEXT FROM loop INTO @TestID,@TestValue

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO #temp (TestID, Te开发者_如何学PythonstValue) values (@TestID, @TestValue)

    FETCH NEXT FROM loop INTO @TestID,@TestValue
END

CLOSE loop
DEALLOCATE loop

    SELECT @TestID FROM #testtable WHERE @TestID > 1

I would suspect that this is what is happening:

• Open a new session in SSMS
• Copy over the above script
• Run it and it works. This is because the #temp table was just created.
• Immediately run it again, or perhaps modify the script a bit and run it again. This time it fails with that error message, because the #temp table was recated in the first pass and has not yet been dropped.

To avoid this problem in your script, at the bottom of the script add:

DROP TABLE #testtable

Instead of using a cursor to populate the temporary table, how about just creating the temp table and populating it in one go like this ?

if OBJECT_ID('tempdb..#testtable') is not null drop table #testtable

SELECT TESTID, TestValue 
INTO #testtable
FROM tblTest WHERE TestID > 1 

If all you need is the sum then this should do it

    SELECT sum(TestValue)
    FROM tblTest
    WHERE TestID > 1