How can I insert rows from one table to another in PL SQL using the cursor for loop?-CodePudding

I don't know how much of this is right

DECLARE    
    CURSOR cur_depts IS
    SELECT *
    FROM dept;
BEGIN
    FOR i IN cur_depts
    LOOP
       INSERT INTO dept_backup
       VALUES(i);
    END LOOP;
    CLOSE  cur_depts;
END;

And this is the error I got

Error report -
ORA-06550: line 8, column 20:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 8, column 8:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I'm using the tables from Scott schema here.

CodePudding user response：

i is the cursor record, you cannot insert it directly to another table, you need to refer to the specific columns. For example,

INSERT INTO dept_backup (b_col1, b_col2, b_col3) values(i.c_col1,i.c_col2,i.c_col3);

CodePudding user response：

You do not need a cursor (and should not use one as individual inserts in a loop will be slower and generate a lot more logging). You can simply use a single INSERT ... SELECT ... statement:

INSERT INTO dept_backup
SELECT * FROM dept;