subconsultas - substring sql

Busque dinámicamente nombres de columna para una tabla mientras está en una consulta sql

Question

Estoy escribiendo SQL (para Oracle) como:

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

donde Schema1.tableA y Schema2.tableA tienen las mismas columnas. Sin embargo, parece que esto no es seguro, ya que el orden de las columnas que regresan en SELECT no está definido. Lo que debería hacer es:

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

Estoy haciendo esto para muchas tablas usando algunos scripts, entonces lo que me gustaría hacer es escribir algo como:

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

Donde foo es una magia ingeniosa que extrae los nombres de las columnas de la tabla uno y los empaqueta en la sintaxis apropiada. ¿Pensamientos?

Answer 1

Este PL / SQL debería hacerlo:

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;
/

Answer 2

Puede necesitar construir las instrucciones de inserción dinámicamente usando USER_TAB_COLUMNS y ejecutarlas usando EXECUTE INMEDIATO .