Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.comcast.com!news.comcast.com.POSTED!not-for-mail NNTP-Posting-Date: Fri, 03 Mar 2006 17:12:30 -0600 Reply-To: "Mark C. Stock" From: "Mark C. Stock" Newsgroups: comp.databases.oracle.server References: <4408c1d2.847343@news.hetnet.nl> <4408c7ac.2345296@news.hetnet.nl> Subject: Re: Reducing coding of insert into.. select statement Date: Fri, 3 Mar 2006 18:12:28 -0500 Organization: Enquery, Incorporated X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-ID: <_dSdnfUesfXDUJXZRVn-pg@comcast.com> Lines: 77 NNTP-Posting-Host: 68.57.99.188 X-Trace: sv3-V8oxUQ6Uh0kzpsHBPVMN7z0QtKEMg3xGi5N+5aIfY7k4NSjzQ5BYbOJWKAMqx5Gees8tS1SNm18IVc2!YwAxmYnISst4nNjcfo2IlileZPGxqpDGmrggwlfv6StZZjCJtKSuiRefrQ/jeK6Iw1If4HL6RVae!nw== X-Complaints-To: abuse@comcast.net X-DMCA-Complaints-To: dmca@comcast.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.32 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262756 "Jaap W. van Dijk" wrote in message news:4408c7ac.2345296@news.hetnet.nl... : On Fri, 3 Mar 2006 17:37:02 -0500, "Mark C. Stock" wrote: : : > : >"Jaap W. van Dijk" wrote in message : >news:4408c1d2.847343@news.hetnet.nl... : >: On Fri, 3 Mar 2006 12:55:24 -0000, Jeremy : >: 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. some days are like that ;-)