Create table #tmp ( Sales Order ID int, Order Qty int ) GO --simple cursor in sql server Declare @orderid int, @orderqty int -- declare a cursor DECLARE insert_cursor CURSOR FOR SELECT Sales Order Id, Order Qty from Sales.Sales Order Detail WHERE Sales Order ID=43659 -- open cursor and fetch first row into variables OPEN insert_cursor FETCH NEXT FROM insert_cursor into @orderid,@orderqty -- check for a new row WHILE @@FETCH_STATUS=0 BEGIN -- do complex operation here Insert into #tmp SELECT @orderid,@orderqty -- get next available row into variables FETCH NEXT FROM insert_cursor into @orderid,@orderqty END close insert_cursor Deallocate insert_cursor GO The above query creates a temporary table #tmp.The import process was slow and had to be done in small chunks for reasons I won’t get into now.It wasn’t until after sleeping on it that I realized I had put myself into the ATG box unnecessarily. I didn’t know much PL/SQL but some Googling (I love how that’s a common verb now, btw) led me to write up this: declare i number := 0; begin for r in (select id from dps_user) loop update dps_user set password = lower(password) where id = r.id; i := i 1; if mod(i, 10000) = 0 THEN -- Commit every 10000 records COMMIT; end if; end loop; commit; end; I’m no Oracle DBA, but it works!When a cursor is based on a join, only the (SELECT MAX(Rate Change Date) FROM Human Resources. CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR) AS BEGIN OPEN p_recordset FOR SELECT ename, empno, deptno FROM emp WHERE deptno = p_deptno ORDER BY ename; END Get Emp RS; / Dim conn, cmd, rs Set conn = Server. Open "DSN=TSH1; UID=scott; PWD=tiger" Set cmd = Server.
Query the #tmp table to verify the insert as shown below.
Create Parameter ("deptno", ad Integer, ad Param Input) cmd.
Command Type = 4 'ad Cmd Stored Proc Dim param1 Set param1 = cmd.
statements might credit one bank account and debit another.
It is important not to allow one operation to succeed while the other fails.
When you use a Transact-SQL server cursor, you can update the current row by using an UPDATE statement that includes a WHERE CURRENT OF clause.