Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Reducing coding of insert into.. select statement

Re: Reducing coding of insert into.. select statement

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 3 Mar 2006 07:58:36 -0500
Message-ID: <p9ydnXnZo57joJXZnZ2dnUVZ_tudnZ2d@comcast.com>

"Jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.1e724ce1b06d6fe698a102_at_news.individual.net...
: Hi folks
:
: To insert data from tab1_at_dblink into tab1 one can code simply:
:
: insert into tab1
: select *
: from tab1_at_dblink;
:
: Now assume that the first column is called ID and is populated from a
: sequence SEQ and that we need to generate a new ID value for every row
: inserted, we have to code
:
: insert into tab1 (ID, col1, col2,col3)
: select SEQ.nextval, col1, col2, col3
: from tab1_at_dblink;
:
: Now if tab1e has 100 coulmns that is a lot of column names to type.
:
: I was just wondering if there was any "quick" way of coding something
: like this where you only had to specify the target columns explicitly
: where their value was to be derived rather than directly copied from the
: source table?
:
: I don't think there is but thought I'd just see if anyone had any
: suggestions. Reason for asking is because we are knocking-up some
: scripts for copying data (subset of) between two databases and obviously
: need ot derive new IDs (and of course retain referential integrity).
:
:
: --
: jeremy
:
: We use Oracle 9iR2 on Solaris 8 with the Oracle HTTP Server and
: mod_plsql

you could use the data dictionary to generate the script

++ mcs Received on Fri Mar 03 2006 - 06:58:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US