Very Computer

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.

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.

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

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.