On Thu, 29 Apr 1999, Daniel Vizel wrote:

> There are column names which have a "#" character suffix to identify
> keys in an Oracle database. Is there an easy way to create equivalent
> tables prefixed with "N_" with column names changed to remove the "#"
> character? Once new tables are created I need to copy the data from the
> original table to the new. There are around 50 tables which need to be
> copied.
> 
Daniel,

You can create the new table and move the data all in one SQL statement.
Suppose that you have a table:

Table_A
COLUMN_1#   VARCHAR2(200)
COLUMN_2#   NUMBER
COLUMN_3    NUMBER

and you want to create Table_B like
N_COLUMN_1  VARCHAR2(200)
N_COLUMN_2  NUMBER
COLUMN_3    NUMBER

You can issue:

CREATE TABLE Table_B (N_COLUMN_1, N_COLUMN_2, COLUMN_3) AS
SELECT COLUMN_1#, COLUMN_2#, COLUMN3)
FROM Table_A;

Hope that this helps!

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 280+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page m"> Back to Ari Kaplan's Home Page