Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!prodigy.net!nx01.iad01.newshosting.com!newshosting.com!216.196.98.140.MISMATCH!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 06:58:38 -0600 Reply-To: "Mark C. Stock" From: "Mark C. Stock" Newsgroups: comp.databases.oracle.server References: Subject: Re: Reducing coding of insert into.. select statement Date: Fri, 3 Mar 2006 07:58:36 -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: Lines: 43 NNTP-Posting-Host: 68.57.99.188 X-Trace: sv3-WMlavYQVr0WehwkJkSU0f5jjOtgh8YuhN+K5KVldWQNUn15mIjRqyiUvgsJ/xugd6XkpwI02LJDZuED!rCx1VC6zrQ/VDFKm1/0VOUFMY8oA+377qUaYSWRiruTmBqOlv5aGt/gxATfbVaNqgD+nOyFWUI1G!TMsTCHEd4UZJ5fo= 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:262684 "Jeremy" wrote in message news:MPG.1e724ce1b06d6fe698a102@news.individual.net... : 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 you could use the data dictionary to generate the script ++ mcs