Re: Reducing coding of insert into.. select statement

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. Received on Fri Mar 03 2006 - 16:53:48 CST