INSERT INTO TAB SELECT * FROM TABLE TAB@db2 -太多的值

Question

我正试图在两个有许多列的表之间移动行。

表列是相同的，除了目标表(tab@db2)有更多的列，这会导致简单的INSERT失败。

我想使用一个简单的PL/SQL语句来动态地构建tab@db2中的列列表，而不是在INSERT和SELECT子句中键入col1, col2等的名称。示例

declare a variable as var_col_list
set col_list = output of select * from tab (omitting rows)
INSERT INTO TAB *var_col_list* SELECT *var_cols_list* FROM TABLE TAB@db2

我已经研究了使用%rowtype，但找不到一个合适的例子，将花费更少的时间比简单地写出列的名称!

任何建议都非常感谢

Answer 1

如果您使用例如TOAD，您可以右键单击表并让它生成语句-在您的情况下，这将是INSERT。您只需稍微修改它(删除不需要的列)，就可以了。

---

否则，您可能会这样做半手动。

这是我的源表:

SQL> SELECT * FROM dept;
DEPTNO DNAME                LOC
---------- -------------------- --------------------
10 ACCOUNTING           NEW YORK
20 RESEARCH             DALLAS
30 SALES                CHICAGO
40 OPERATIONS           BOSTON

目标表不包含所有列:

SQL> CREATE TABLE target
2  (
3     deptno   NUMBER,
4     dname    VARCHAR2 (20)
5  );
Table created.

循环遍历所有TARGET表列(即具有较少列的表)并组成INSERT INTO语句的代码:

SQL> DECLARE
2     l_str  VARCHAR2 (1000);
3  BEGIN
4     FOR cur_r IN (SELECT column_name
5                     FROM user_tab_columns
6                    WHERE table_name = 'TARGET')
7     LOOP
8        l_str := l_str || ', ' || cur_r.column_name;
9     END LOOP;
10
11     l_str :=
12        'insert into target select ' || LTRIM (l_str, ', ') || ' from dept';
13     DBMS_OUTPUT.put_line (l_str);
14
15     EXECUTE IMMEDIATE l_str;
16  END;
17  /
insert into target select DEPTNO, DNAME from dept          --> this is the L_STR contents
PL/SQL procedure successfully completed.
SQL> SELECT * FROM target;
DEPTNO DNAME
---------- --------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

似乎没问题。

Answer 2

使用Littefoot提供的解决方案，我做了一些小调整，以完全满足我的需求:

SQL> create table taba (col1 number,col2 number);
SQL> insert into taba values (1,2);
SQL> select * from taba;
COL1       COL2
---------- ----------
1          2

SQL> create table tabb (col1 number,col2 number, col3 number);
SQL> DECLARE
l_str  VARCHAR2 (32767);
BEGIN
FOR cur_r IN (SELECT column_name
FROM user_tab_columns
WHERE table_name = 'TABA'
order by column_id asc)
LOOP
l_str := l_str || ', ' || cur_r.column_name;
END LOOP;

l_str :=
'insert into tabb (' || LTRIM (l_str, ', ') || ') ' ||' select ' || LTRIM (l_str, ', ') || ' from taba';
DBMS_OUTPUT.put_line (l_str);

EXECUTE IMMEDIATE l_str;
END;
/

l_str (SQL INSERT)的输出:

insert into tabb (COL1, COL2)  select COL1, COL2 from taba

结果:

SQL> select * from tabb;

COL1       COL2       COL3
---------- ---------- ----------
1          2