Re: Reducing coding of insert into.. select statement

"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 Received on Fri Mar 03 2006 - 16:37:02 CST