在sql查询中动态查找表的列名

I'm writing SQL (for Oracle) like:

我正在编写SQL(对于Oracle),如:

INSERT INTO Schema1.tableA SELECT * FROM Schema2.tableA;

where Schema1.tableA and Schema2.tableA have the same columns. However, it seems like this is unsafe, since the order of the columns coming back in the SELECT is undefined. What I should be doing is:

其中Schema1.tableA和Schema2.tableA具有相同的列。但是,似乎这是不安全的,因为SELECT中返回的列的顺序是未定义的。我应该做的是:

INSERT INTO Schema1.tableA (col1, col2, ... colN) 
SELECT (col1, col2, ... colN) FROM Schema2.tableA;

I'm doing this for lots of tables using some scripts, so what I'd like to do is write something like:

我正在使用一些脚本为很多表做这个,所以我想做的是写如:

INSERT INTO Schema1.tableA (foo(Schema1.tableA)) 
SELECT (foo(Schema1.tableA)) FROM Schema2.tableA;

Where foo is some nifty magic that extracts the column names from table one and packages them in the appropriate syntax. Thoughts?

其中foo是一些漂亮的魔术,它从表1中提取列名并以适当的语法打包它们。思考?

2 个解决方案

#1

This PL/SQL should do it:

这个PL / SQL应该这样做:

declare
    l_cols long;
    l_sql  long;
begin
    for r in (select column_name from all_tab_columns
              where  table_name = 'TABLEA'
              and    owner = 'SCHEMA1'
             )
    loop
       l_cols := l_cols || ',' || r.column_name;
    end loop;

    -- Remove leading comma
    l_cols := substr(l_cols, 2);

    l_sql := 'insert into schema1.tableA (' || l_cols || ') select ' 
             || l_cols || ' from schema2.tableA';

    execute immediate l_sql;

end;
/

#2

You may need to construct the insert statements dynamically using USER_TAB_COLUMNS and execute them using EXECUTE IMMEDIATE.

您可能需要使用USER_TAB_COLUMNS动态构造insert语句,并使用EXECUTE IMMEDIATE执行它们。

#1