Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.glorb.com!feeder2.on.meganewsservers.com!meganewsservers.com!textfeed1.on.meganewsservers.com!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:25:41 GMT
Message-ID: <4408c1d2.847343@news.hetnet.nl>
References: <MPG.1e724ce1b06d6fe698a102@news.individual.net>
X-Newsreader: Forte Free Agent 1.21/32.243
Lines: 46
NNTP-Posting-Host: 86.82.143.232
X-Trace: 1141424741 text.nova.planet.nl 2014 86.82.143.232:59123
X-Complaints-To: abuse@planet.nl
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262745

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.