Re: Update inside of a select - any cursor problems?

["Bencke, Gina" <GBencke@KICHLER.COM>]

Bob,

I believe the rumor is false. DDL statements (e.g. CREATE TABLE) will
close cursors in some databases. I have never heard of DML
(INSERT/UPDATE/DELETE) causing such problems.

I use the approach you are talking about all the time in Oracle and
DB/2.

Gina
-----Original Message-----
From: Bob Buford-Abba Systems [mailto:bbuford@GALEIND.COM]
Sent: Friday, October 29, 1999 3:14 PM
To: Multiple recipients of list SQR-USERS
Subject: Update inside of a select - any cursor problems?


Hi all,
I am working with PS HRMS 7.5, with MSSQL Server and SQR 4, and have a
question about doing SQL updates or inserts inside of a select loop -
what effect it might have on the select cursor.
I need to take information from one table (GL extract) and put it into a
temporary table for reporting purposes. The GL table has paycheck
information (page#, line#, pay end date, paygroup, but of course not
EMPLID). The reporting table, which is a custom design, needs to have
Location, which is not part of the GL table.
I don't want to modify or replace the GL table, if I can avoid it.
My current thought (of course, also, no real data yet with which to
test) is to set up a SELECT from the GL table and within that select,
call another procedure which updates the reporting table. I heard
somewhere that doing an insert / update / delete in effect closes other
cursors, so this might break the SELECT cursor.
Any truth to that rumor, as applies to SQL Server?
Thanks,
Bob