Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
204
questions
0
votes
0
answers
33
views
Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows
1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values:
[a,b,Null], [c,d,Null], [e,f,Null]
2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having ...
- 21
-1
votes
1
answer
51
views
SQL Server Cursor preceed by IF statement? [closed]
The SELECT statement to populate my Sql Server CURSOR can (often) return zero rows.
Is it advisable to enclose it within an IF check so the CURSOR it is not Declared, Opened, Closed, & Deallocated ...
- 21
-1
votes
2
answers
316
views
SQL Cursor vs WHILE loop
When you need to loop a set of data, is there a benefit of using a SQL Server cursor. Or is using a WHILE loop (see below) just the same.
I ask because the WHILE loop seems clearer and easier to ...
- 503
0
votes
0
answers
65
views
Update child tables when an id field updated In main table
I have this sp to delete child from child tables when something deleted from the main table .
Now I want the same function with update statement . When I update the id field on main table . I want to ...
- 89
0
votes
0
answers
126
views
How to use variable in the select query PostgreSQL
I'm looking for a way to write a recursive query, but I'm getting an error.
"SQL Error [42601]: ERROR: syntax error at or near "with recursive"
Position: 287"
query result "...
- 1
1
vote
0
answers
68
views
How to get the columnLabel (not columnName) from a cursor
I've seen code to do it with a result set, but the Python code I am using has a cursor not a result set. It is thus printing the wrong labels for the columns.
select c1 as Hello, c2 as World from ...
- 141
0
votes
0
answers
189
views
Syntax error near declare statement Cursor %TYPE
As a beginner, I m playing with PL/SQL where I am simply displaying list of employees. however, I am facing this syntax error issue near where I wrote declare statement, I am not able to figure out ...
- 101
2
votes
1
answer
59
views
Insert rows into a table from another table row?
I've got a table Schedule
Id
unserId
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
0
10
0
1
1
1
1
0
0
1
20
1
0
0
0
0
0
0
I need to insert lines from one row in table Schedule into seven ...
- 302
-1
votes
1
answer
3k
views
How to use temp table or while loop instead of cursor
i have an sp to delete data from grandchildren tables .
But I need to change the cursor and use a temp table or while loop instead of the cursor. I have tried but was not working can somebody help.
Sp ...
- 57
0
votes
1
answer
131
views
Loop through schemas and execute dml on the same table in each schema
I have a table "active_t" in a schema called "test", having the following structure:
instance active
IN 1
SI 0
The ask is to loop ...
- 1
2
votes
1
answer
240
views
Procedure not looping properly
I have several client databases on one server. They all have a prefix. Lets use "piggy_" as the prefix for the purposes of this.
I.e.
piggy_client_a
piggy_client_b
piggy_client_c
Below is ...
- 123
4
votes
0
answers
116
views
Log Backup occasionally blocked by automated update
We have a canary table in each of our databases that our automation updates once a minute. The automation is a sql server agent job that utilizes a cursor to iterate over the databases and then ...
- 3,028
4
votes
1
answer
604
views
Why does CURSOR_STATUS return unexpected results for an output cursor inside the stored procedure that created it?
I have a stored procedure that has an output parameter of the CURSOR VARYING type. I would like to verify that the output cursor can be used by the code that called the stored procedure. It seemed ...
- 31.8k
0
votes
0
answers
3k
views
How do I loop through a cursor in Redshift?
Rather than pollute this thread with the dozens of different things I've tried, including using a stored procedure, dynamic SQL, etc., all of which results in one error or another, I will ask what is ...
- 294
2
votes
1
answer
145
views
Query Performance for Reports in PostgreSQL
I use PostgreSQL 12 to store my raw data and ElasticSearch as the datawarehouse for reports.
It happens that there are situations with JOIN (eg, data that exists in one table but does not exist in ...
- 406
4
votes
1
answer
393
views
Changing SET options within cursor - Different behavior based on compatibility level
I have found a difference in behavior on a piece of code between database compatibility levels and wondered what the cause of this is. Below is a simple example that iterates through a tally table and ...
- 1,898
0
votes
1
answer
2k
views
Executing a query with a variable in a loop
I have two select, how can I do the same using a loop and a variable in PL SQL Developer.
I have:
select 1 as nm from dual;
select 2 as nm from dual;
I need something similar:
DECLARE x NUMBER;
BEGIN
...
- 1
0
votes
0
answers
529
views
Trying to read a json lines file with OPENROWSET and cursor
I've got a pile of json lines files that I'm trying to process into a set of Sql Server database tables. Can't use OPENJSON() because jsonlines is not valid in total, only line by line. Plus the ...
- 307
2
votes
2
answers
2k
views
SELECT... with LIMIT, but iterate forward getting other records?
I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g.
$$
DECLARE
BEGIN
LOOP
exit when get diagnostics n_rec_count = 0;
...
- 121
0
votes
1
answer
2k
views
Procedure insert into another table using cursor [closed]
I'm creating a procedure, using cursor, which checks in different tables if the current quantity of 5 products are below their minimum quantity. If the current quantity is below I create a PURCHASE ...
0
votes
0
answers
350
views
How to transfer data from one table to another with exception handling in Postgresql?
So, I have two tables, one where I import data from an excel file all in string format without considering any sorts of constraints or data type validation. And then the data is transferred to the ...
- 111
0
votes
1
answer
489
views
Creating a dynamic db2 reorg script with in-built delay for concurrency
So I'm new to db2, and some of the syntax and other execution restrictions give me trouble. My DB is using DB2 v11.1.2.2. I've created a reorg script which calls the REORGCHK_TB_STATS and ...
- 1,803
0
votes
1
answer
1k
views
How do I get DBeaver to do a DB2 UPDATE via Cursor
The following query works when run from the console via db2 command. But fails when running via DBeaver.
BEGIN NOT ATOMIC
FOR R AS C CURSOR WITH HOLD FOR
SELECT ROW_NUMBER() OVER() AS INDEX, ID
...
- 35
0
votes
0
answers
43
views
How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor
Please refer to the following schema at SQL Fiddle when reading the use case below.
In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
- 1,913
0
votes
4
answers
179
views
use a string array without CREATE privileges
I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table.
select 'Accounts', row_id Acct_id, pr_postn_id
from schema....
- 119
1
vote
2
answers
1k
views
How can I efficiently delete records when a counter reaches zero in PostgreSQL?
Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
1
vote
1
answer
651
views
Inserting Data using Cursor but check if the data already in the table
I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, ...
- 51
0
votes
1
answer
427
views
Replicate multiple times a set of rows with unique column in a table
I wish to replicate a set of rows in a table but with handling unique column in SQL Server 17.
The table is:
CREATE TABLE test1 (
id int CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED IDENTITY,
...
- 103
1
vote
2
answers
765
views
Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?
I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to ...
- 1,913
0
votes
1
answer
184
views
Preserve capitalization of column names/keys returned by SYS_REFCURSOR in Oracle
I am using a SYS_REFCURSOR to return JSON data to an ORDS endpoint. I need the case of the returned column names/JSON keys to be preserved for use in my node application. I have tried the following:
...
- 23
1
vote
1
answer
498
views
How to improve performance merging multiple table via cursor
I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query
BEGIN
DECLARE done INT ...
- 111
-1
votes
1
answer
358
views
Stored Proc hangs when no rows exist in cursor? [closed]
I'm on 8.0.19 if that matters.
Here is my test proc:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `testNullProc`()
main:BEGIN
DECLARE fileID INTEGER;
DECLARE finished INTEGER ...
- 21
1
vote
0
answers
325
views
How to write Procedure to fetch data from other table?
drop procedure if exists findClosestTimeStamp;
delimiter $$
create procedure findClosestTimeStamp()
begin
declare tt1 time;
declare dt1 date;
declare view1 varchar(30);
declare done int ...
- 11
0
votes
1
answer
2k
views
Set variable inside cursor for to use in select statement [closed]
This is my query to create procedure, but its displaying error like :
'DECLARE curBalRows CURSOR FOR
select if(
What I'm missing here?
DELIMITER $$
drop procedure if exists `...
- 101
1
vote
1
answer
2k
views
MySQL - Pass input parameter into Cursor query
Is it possible to pass input parameter into Cursor
SELECT statement WHERE clause?
For some reason I think it isn't working.
I'm trying to pass _TAG and _ITEM_NAME into where clause.
DECLARE ...
- 111
0
votes
1
answer
2k
views
Get availability group and listener of the current DB (dynamic SQL)
I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
- 349
0
votes
0
answers
50
views
Is it possible for a FOR LOOP cursor execute operations asynchronously?
I was having a problem in which the number in a table isn't updated between each commit, it was supposed to be incremented 1 by 1 but it was been incremented 2 by 2, I included logs between the ...
- 101
0
votes
1
answer
1k
views
MySQL cursor always exits out of loop
The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop.
Anything wrong here?
I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it ...
- 111
4
votes
1
answer
558
views
Cursoring over sys.databases skips databases
I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:
I have the following code to perform database backups.
...
- 1,898
0
votes
2
answers
247
views
Overview of leaked/unclosed cursors/statements on DB2 LUW server?
We're encountering a "too many open cursors issue" on DB2/LUW.
Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors?
We're doing ...
- 101
2
votes
1
answer
486
views
Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?
In this example:
DECLARE @KeyField INT, @ValueField INT
DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.ValueField = ...
- 34k
0
votes
1
answer
117
views
Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows
I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' ...
0
votes
2
answers
833
views
How to drop a procedure across a lot of databases (SQL Server 2016)
I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete ...
- 1,407
-2
votes
1
answer
137
views
SQL Server: Use Implicit or Explicit Cursor? [closed]
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHILE (1=1)
BEGIN
EXEC 'drop view ' + Table_Name
END
or
DECLARE my_cursor CURSOR FOR
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
OPEN ...
- 115
0
votes
1
answer
714
views
Procedure MySQL cursor taking a long time to execute
I have a MySQL procedure, with a cursor in it. The sample cursor query is given below
DECLARE cs CURSOR FOR
(SELECT a.mag_id FROM A a WHERE
a.creation_date BETWEEN (...
8
votes
1
answer
331
views
If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?
If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
- 34k
0
votes
2
answers
2k
views
Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server
I get this error when I execute this cursor.
Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared.
But ...
- 13
1
vote
1
answer
713
views
Safeguard against infinite looping in case of missing FETCH NEXT
It was found that this code mistake caused an infinite loop in production:
DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @...
- 711
0
votes
0
answers
710
views
Must declare the scalar variable in cursor SQL SERVER
I have this problem with this code.
I don't know why, because before set values.I declare a variable.
Moreover, How i can execute every results that cursor give
Error message: Msg 137, Level 15, ...
- 13
0
votes
1
answer
54
views
Insert top 253 values where value in select distinct tradernames
I am trying to insert top 253 values for each trader from a temp traders table into temp results table.
I am using following code to do it.
CREATE TABLE #trad ( Trad varchar(50))
IF @trad is ...
- 93