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: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Fri, 03 Mar 2006 22:25:41 GMT
Message-ID: <4408c1d2.847343@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. Received on Fri Mar 03 2006 - 16:25:41 CST

Original text of this message

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