Re: INSERT INTO TABLE that has mismatched structure.

From: Charlie Edwards <Charlie3101_at_hotmail.com>
Date: 7 Jun 2002 02:20:01 -0700
Message-ID: <db479d88.0206070120.66fc4e8_at_posting.google.com>

factor_at_mail.ru (Kevin) wrote in message news:<eb7b37e9.0206041619.424b3bee_at_posting.google.com>...

> Hello!

>
> I want to append records from one table to another, that contain more
> fields.
>
> I try to make this:
> INSERT INTO K_PR_ARX SELECT * FROM k_pr1000;
>
> and get this message: ORA-00947 not enough values.
> This happened because the table k_pr1000 has got less fields than the
> table K_PR_ARX. I think exist other way to make this thing (may be
> hints, etc).
>
> Excuse me for my bad english.
> Please help me.
>
> Kevin.
> Russia.

Does this help (it's messy, but it works)? It creates a SQL statements for copying all columns from one table to another providing the columns have the same name.

CE

Accept fromtable prompt 'Which table do you wish to copy from? ' Accept totable prompt 'Which table do you wish to copy to? '

DECLARE
   CURSOR c1 IS

      SELECT utc1.column_name
        FROM user_tab_columns utc1,
             user_tab_columns utc2
       WHERE utc1.column_name = utc2.column_name
         AND utc1.table_name = UPPER('&fromtable')
         AND utc2.table_name = UPPER('&totable');

   comma VARCHAR2(1) := ' ';
BEGIN
   DBMS_OUTPUT.PUT_LINE('INSERT INTO '||LOWER('&totable')||' (');    FOR r1 IN c1 LOOP

      DBMS_OUTPUT.PUT_LINE(comma||LOWER(r1.column_name));
      comma := ',';

   END LOOP;
   DBMS_OUTPUT.PUT_LINE(')');
   DBMS_OUTPUT.PUT_LINE('SELECT');
   comma := ' ';
   FOR r1 IN c1 LOOP

      DBMS_OUTPUT.PUT_LINE(comma||LOWER(r1.column_name));
      comma := ',';

   END LOOP;
   DBMS_OUTPUT.PUT_LINE('FROM '||LOWER('&fromtable')||';'); END;
/ Received on Fri Jun 07 2002 - 11:20:01 CEST