Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news-han1.dfn.de!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!newsfeed.freenet.de!213.132.189.2.MISMATCH!multikabel.net!feed20.multikabel.net!tudelft.nl!txtfeed2.tudelft.nl!eweka.nl!lightspeed.eweka.nl!newsfeeder.wxs.nl!divide.nova.planet.nl!posting.nova.planet.nl!not-for-mail
From: j.w.vandijk.removethis@hetnet.nl (Jaap W. van Dijk)
Newsgroups: comp.databases.oracle.server
Subject: Re: Reducing coding of insert into.. select statement
Date: Fri, 03 Mar 2006 22:53:48 GMT
Message-ID: <4408c7ac.2345296@news.hetnet.nl>
References: <MPG.1e724ce1b06d6fe698a102@news.individual.net> <4408c1d2.847343@news.hetnet.nl> <APednZJ7ha-MWJXZRVn-qw@comcast.com>
X-Newsreader: Forte Free Agent 1.21/32.243
Lines: 67
NNTP-Posting-Host: 86.82.143.232
X-Trace: 1141426427 text.nova.planet.nl 2018 86.82.143.232:59162
X-Complaints-To: abuse@planet.nl
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262753

On Fri, 3 Mar 2006 17:37:02 -0500, "Mark C. Stock" <mcstockX@Xenquery
.com> wrote:

>
>"Jaap W. van Dijk" <j.w.vandijk.removethis@hetnet.nl> wrote in message 
>news:4408c1d2.847343@news.hetnet.nl...
>: On Fri, 3 Mar 2006 12:55:24 -0000, Jeremy <jeremy0505@gmail.com>
>: wrote:
>:
>: >Hi folks
>: >
>: >To insert data from tab1@dblink into tab1 one can code simply:
>: >
>: >insert into tab1
>: >select *
>: >from   tab1@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@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@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.