CURSORS AND TEMPORARY TABLES !!!

CURSORS AND TEMPORARY TABLES !!!

Post by Nandino Garci » Wed, 14 Mar 2001 23:14:23



Does anyone know if you can create a cursor on temporary table and if it can
be done, how so ?
Further, what are the pros and cons of using a temporary table instead of a
normal table.

Any assistance will be greatly welcomed.

Thanks.

 
 
 

CURSORS AND TEMPORARY TABLES !!!

Post by Tibor Karasz » Thu, 15 Mar 2001 19:05:10


Nandino,

You can certainly create a cursor on a temporary table. No example is necessary. Use
any from Books Online, just create a temp table and have the cursor operate on that
temptable instead of an ordinary table.

Advantages of using temptable includes:
* Automatic cleanup. temptable disappears when connection is terminated.
* A little bit less I/O. SQL Server doesn't have to log REDO operations to tempdb's
transaction log.

Of course, avoiding cursors and temptable altogether, in favor for a set based
approach, is in general best.
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


Quote:> Does anyone know if you can create a cursor on temporary table and if it can
> be done, how so ?
> Further, what are the pros and cons of using a temporary table instead of a
> normal table.

> Any assistance will be greatly welcomed.

> Thanks.


 
 
 

CURSORS AND TEMPORARY TABLES !!!

Post by BenW » Sat, 17 Mar 2001 00:59:53


Tibor,

Sorry to jump in; Tibor, could you provide additional info on the set-based
approach?  It sounds very interesting, are additional references avaialble?

thx

-----Original Message-----
Nandino,

You can certainly create a cursor on a temporary table. No example is necessary.
Use
any from Books Online, just create a temp table and have the cursor operate on
that
temptable instead of an ordinary table.

Advantages of using temptable includes:
* Automatic cleanup. temptable disappears when connection is terminated.
* A little bit less I/O. SQL Server doesn't have to log REDO operations to
tempdb's
transaction log.

Of course, avoiding cursors and temptable altogether, in favor for a set based
approach, is in general best.
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.



> Does anyone know if you can create a cursor on temporary table and if it can
> be done, how so ?
> Further, what are the pros and cons of using a temporary table instead of a
> normal table.

> Any assistance will be greatly welcomed.

> Thanks.

.

 
 
 

CURSORS AND TEMPORARY TABLES !!!

Post by Tibor Karasz » Sat, 17 Mar 2001 17:01:48


Ben,

Quote:> Tibor, could you provide additional info on the set-based
> approach?  It sounds very interesting, are additional references avaialble?

I'm afraid that I can't. It is a way of thinking more than a standard technique to be
generally applied. It comes with experience of using SQL and wanting to grow as a SQL
programmer. Here's an example of what I mean:

Assume that you want to increase discount with 10% for all customer who have bought
for more than $10,000. Assume 1,000,000 rows and 1,000 customers bought for more than
$10,000:

Approach 1:
Use a cursor to loop each customer.
If bougtfor > $10000 execute an UPDATE based on current custno
You will bring back 1000000 rows to client and issue 1000 UPDATE statements.

Approach 2:
UPDATE customers
SET boughtfor = boughtfor * 1.1
WHERE boughtfor > $10000

Above is a dead simple case, and even as such it is simplified. But it serves its
purpose to explain that there isn't a standard technique to apply. It is a way of
thinking.
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

 
 
 

1. Cursor vis Temporary table

hi,
  I want to create one batch programme (Background
process) which will process more than 5,00,000 records.
It will use lot of insert,update,delete operation.

Which way is better, using Cursor (or) creating as many as
temporary table and using that Temporary table? I want
explanation also.

Any body help?

Thanks,
Ramesh

2. List of values in one parameter??

3. CURSORS AND TEMPORARY TABLES !!!

4. FREE stuff

5. Cursor and temporary table vs ADO client

6. VIPER

7. DESPERATE!!! Basing a cursor on temporary table

8. Parsing a MDX Caculated members sintax

9. Can't create an update cursor on a temporary table

10. Cursors v/s Temporary Tables

11. Cursor vs. Temporary Tables

12. Populating a temporary table with a cursor while looping through dates

13. Stored procedures, cursors, temporary tables, and the EXECUTE statment