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 18:12:28 -0500
Message-ID: <_dSdnfUesfXDUJXZRVn-pg@comcast.com>

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:4408c7ac.2345296_at_news.hetnet.nl...
: On Fri, 3 Mar 2006 17:37:02 -0500, "Mark C. Stock" <mcstockX_at_Xenquery
: .com> wrote:
:
: >
: >"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
: >news:4408c1d2.847343_at_news.hetnet.nl...
: >: On Fri, 3 Mar 2006 12:55:24 -0000, Jeremy <jeremy0505_at_gmail.com>
: >: wrote:
: >:
: >: >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
: >:
: >: If you put an alias on the remote table you can use that in the SELECT
: >: list:
: >:
: >: insert into tab1
: >: select SEQ.nextval,remtab.*
: >: from tab1_at_dblink remtab;
: >:
: >: Jaap.
: >
: >but then you've got the new value from the sequence and the PK -- from
the
: >OP's first example, you'd end up with:
: >
: >insert into tab1 (ID, col1, col2,col3)
: >select SEQ.nextval,ID, col1, col2, col3
: >
: >++ mcs
: >
: >
: You're right. I erroneously read that the local table differed from
: the remote because it had an extra ID column that wasn't present in
: the remote table.
:
: Jaap.

some days are like that ;-) Received on Fri Mar 03 2006 - 17:12:28 CST

Original text of this message

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