Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!nntp.theplanet.net!inewsm1.nntp.theplanet.net!newsfeed00.sul.t-online.de!t-online.de!newsfeed.r-kom.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Jeremy Newsgroups: comp.databases.oracle.server Subject: Reducing coding of insert into.. select statement Date: Fri, 3 Mar 2006 12:55:24 -0000 Lines: 34 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit X-Trace: individual.net xwHUKPUgZEcKlZw/JiKXgAisJbxB+WsQ/xcCnglIC90zmu3b4c User-Agent: MicroPlanet-Gravity/2.60.2060 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262683 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