Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Beginner - copying data from one table to another
The plot thickens...
Are you trying to write an upgrade script that you can send out to customer sites, that will migrate data from the previous schema design to the new schema design?
You said you don't know the exact layout of the table at the customer site, but you know you need to transfer all data between namesake columns from old table to new table (BTW, tablespace is irrelevant but table owner is relevant). Can you be confident that the data types are compatible? Obviously, trying to copy data from old_table.name varchar2(100) into new_table.name char(30) will only lead to errors.
I'm thinking that an automated script solution could be achieved by employing the "SQL-from-SQL" technique. In SQL*Plus, you can write queries against the Oracle data dictionary views, using a self-join to identify matching column names, and wrapping the output with string literals so as to generate the necessary SQL statements. Your output is then spooled to a file which you can execute as a SQL*Plus script.
As a start, check out the data dictionary views ALL_TABLES, ALL_TAB_COLUMNS.
I don't have access to a database right now, but here's a simple example (untested) of sql-from-sql to try out:
select 'select null' from dual
union all
select ', ' || column_name
from all_tab_columns
where table_name = '&TAB1'
union all
select 'from &tab1' from dual
HTH
Martin Doherty
Mark Muller wrote:
>Thanks,
>
>Oracle 9i
>
>The problem is tab2 is the current table used for a program, tab1 is the
>current table used at a customer site
>I was looking for the command to copy the data where column names are the
>same, without nowing the exact layout of Tab1, but i know the name (and
>tablespace) of the old and new table
>
>
>
>
>
>>The simplest answer is to simply write an INSERT statement with a
>>subquery instead of a VALUES clause
>>e.g.
>>INSERT INTO tab2 (ID, NAME)
>>SELECT ID, NAME FROM tab1
>>
>>
>
>
>
>>>Tab1
>>> ID CHAR(10)
>>> NAME CHAR(30)
>>> PHONE CHAR(15)
>>>
>>>Tab2
>>> ID CHAR(10)
>>> FNAME CHAR(10)
>>> NAME CHAR(30)
>>>
>>>Can i write a script that will insert all rows from Tab1 into Tab2 for
>>>
>>>
>the
>
>
>>>identical column names
>>>
>>>
>
>
>
>
Received on Fri Dec 06 2002 - 03:28:02 CST