1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| SQL> declare
2 l_query long;
3 begin
4 for c in (select table_name, listagg(column_name, ',') within group (order by column_id) as list_column
5 from all_tab_columns
6 where owner = 'SCOTT'
7 and table_name in ('EMP', 'DEPT')
8 group by table_name) loop
9 l_query := 'insert into ' || c.table_name || ' ( ' || c.list_column || ') '||chr(10);
10 l_query := l_query || 'select ' || c.list_column || ' from scott.'||c.table_name;
11 dbms_output.put_line (l_query);
12 execute immediate l_query;
13 end loop;
14 end;
15 /
insert into DEPT ( DEPTNO,DNAME,LOC)
select DEPTNO,DNAME,LOC from scott.DEPT
insert into EMP ( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from scott.EMP
PL/SQL procedure successfully completed.
SQL> |
Partager