Here is a PL/pgSQL block that uses an explicit cursor to perform the operation you described:

```sql
DO $$
DECLARE
cur CURSOR FOR SELECT ano, customer_name, balance FROM account WHERE branch_name = 'surat';
rec RECORD;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO rec;
EXIT WHEN NOT FOUND;

INSERT INTO branch_surat (ano, customer_name, balance) VALUES (rec.ano, rec.customer_name, rec.balance);
DELETE FROM account WHERE CURRENT OF cur;
END LOOP;
CLOSE cur;
END $$;
```

This block declares a cursor `cur` for the query that selects all records from the `account` table where `branch_name` is 'surat'. It then opens the cursor and enters a loop. In each iteration of the loop, it fetches the next record from the cursor into the `rec` record variable. If no more records are found, it exits the loop.

Then it inserts the fetched record into the `branch_surat` table and deletes the same record from the `account` table. After all records have been processed, it closes the cursor.

Please note that this code assumes that the `ano` field is the primary key in both tables, and that there are no foreign key constraints that would prevent the deletion of records from the `account` table. If such constraints exist, you will need to handle them appropriately in your code.

Question

Here is a PL/pgSQL block that uses an explicit cursor to perform the operation you described:

```sql
DO $$
DECLARE
    cur CURSOR FOR SELECT ano, customer_name, balance FROM account WHERE branch_name = 'surat';
    rec RECORD;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        INSERT INTO branch_surat (ano, customer_name, balance) VALUES (rec.ano, rec.customer_name, rec.balance);
        DELETE FROM account WHERE CURRENT OF cur;
    END LOOP;
    CLOSE cur;
END $$;
```

This block declares a cursor `cur` for the query that selects all records from the `account` table where `branch_name` is 'surat'. It then opens the cursor and enters a loop. In each iteration of the loop, it fetches the next record from the cursor into the `rec` record variable. If no more records are found, it exits the loop.

Then it inserts the fetched record into the `branch_surat` table and deletes the same record from the `account` table. After all records have been processed, it closes the cursor.

Please note that this code assumes that the `ano` field is the primary key in both tables, and that there are no foreign key constraints that would prevent the deletion of records from the `account` table. If such constraints exist, you will need to handle them appropriately in your code.

Knowee AI · Accepted Answer

Here is a PL/pgSQL block that uses an explicit cursor to perform the operation you described:

```sql
DO $$
DECLARE
    cur CURSOR FOR SELECT ano, customer_name, balance FROM account WHERE branch_name = 'surat';
    rec RECORD;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        INSERT INTO branch_surat (ano, customer_name, balance) VALUES (rec.ano, rec.customer_name, rec.balance);
        DELETE FROM account WHERE CURRENT OF cur;
    END LOOP;
    CLOSE cur;
END $$;
```

This block declares a cursor `cur` for the query that selects all records from the `account` table where `branch_name` is 'surat'. It then opens the cursor and enters a loop. In each iteration of the loop, it fetches the next record from the cursor into the `rec` record variable. If no more records are found, it exits the loop.

Then it inserts the fetched record into the `branch_surat` table and deletes the same record from the `account` table. After all records have been processed, it closes the cursor.

Please note that this code assumes that the `ano` field is the primary key in both tables, and that there are no foreign key constraints that would prevent the deletion of records from the `account` table. If such constraints exist, you will need to handle them appropriately in your code.

Question

Solution

Similar Questions

Upgrade your grade with Knowee