How do I loop through the records in a temporary table

How do I loop through the records in a temporary table?
ThanksHi Tim
Processing rows is usually less efficent than using set based commands on
your data set, therefore if you can use a set based solution is should be
better. You can use a CURSOR on a temporary table for example:
SELECT LastName, FirstName
INTO #Employees
FROM Northwind.dbo.Employees
WHERE LastName like 'B%'
DECLARE @.LastName [nvarchar] (20) ,
@.FirstName [nvarchar] (10)
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM #Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @.LastName, @.FirstName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.FirstName + N' ' + @.LastName
FETCH NEXT FROM Employee_Cursor INTO @.LastName, @.FirstName
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
DROP TABLE #Employees
Check out information on DECLARING/OPENING/CLOSING/DEALLOCATING and FETCHING
from cursors in Books Online
John
"Tim Kelley" wrote:
> How do I loop through the records in a temporary table?
> Thanks
>
>

How do I insert into existing Temp table?

Hi,

How do I insert data into an existing temporary table? Note: Im primarily a .NET programmer who has to do T-SQL to grab data from time to time.

What I am trying to do is this:
1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).

I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I dont want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.

If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.

-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

My problem is that #GroupConfidence already exists so in fact I have this code below:

CREATE TABLE #GroupConfidence
( [FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubmitID] [int] NOT NULL,
[GL_Score] [decimal](19, 10) NOT NULL,
[GP_Score] [decimal](19, 10) NOT NULL,
[GPH_Score] [decimal](19, 10) NOT NULL,
[DL_Score] [decimal](19, 10) NOT NULL,
[MP_Score] [decimal](19, 10) NOT NULL,
[Role_MI_Score] [decimal](19, 10) NOT NULL,
[Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
[Role_APRA_Score] [decimal](19, 10) NOT NULL,
[AllCategories] [decimal](19, 10) NOT NULL
)

-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
There is already an object named '#GroupConfidence' in the database.

Thanks in advance,

Ian.SELECT .... INTO <NEWTABLE> FROM <ANOTHER TABLE> will create the table and then insert the rows of the SELECT statement. You are getting the error because first you had created the table using "CREATE TABLE" statement then again are using SELECT ... INTO statment to create the table and insert the rows.

If you want to perform CREATE and INSERT operations in two statements then you can execute below statements:

CREATE TABLE #GroupConfidence ...

INSERT INTO #GroupConfidence SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories' FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

Hope the above helps you.|||EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.

So thanks for this.

Ian.

:D

How do I disable transaction logging in a stored procedure?

Greetings,

I would like to turn off transaction logging for a long-running stored procedure. It is doing some one-shot utility work in a temporary table. If the stored procedure fails then I'll delete all the rows in the table.

Is is possible to turn off (or at least minimize) the logging for a single stored procedure? I'm looking at this from a performance perspective, thinking that doing no logging will speed things up a bit. The stored procedure will potentially update hundreds of thousands rows.

Am I on the right track or barking up the wrong tree?

Thanks,

BCB

hello,

everything that happens in the database are entered into the transaction log

when you are in the full recovery.

you may only choose any of the three recovery model to handle the log size

1. simple

2. bulk logged

3. fully logged

you can deal with the logs only as a part of your db maintenance procedure.

thanks,

joey