Its not a bad Idea but I was trying to get away from specifying any fields
at all in the insert so that there are no changes if we add fields to the
table.
If I
Insert into table1 as select
decode(key_field,old_value,new_value,old_value), field2, field3 from table1
where old_value = 1;
Then I have to specify each of the fields in teh select statement.
I really want to open a cursor, select a record, modify the key field, then
insert the entire record without specifying fields at all.
Thanks
Kevin
-----Original Message-----
Sent: Thursday, August 02, 2001 11:46 PM
To: Multiple recipients of list ORACLE-L
hi,
try
INSERT INTO yournewtable
SELECT * FROM youroldtable;
if you want to modify value for particular field you can use oracle
functions in SELECT clause.
in example below I use string function called SUBSTR :
SQL> insert into newdept
2 select deptno,SUBSTR(dname,1,3) ,loc from olddept;
SQL> select * from olddept;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from newdept;
DEPTNO DNA LOC
--------- -- -------------
10 ACC NEW YORK
20 RES DALLAS
30 SAL CHICAGO
40 OPE BOSTON
hope this help : )
<> Bambang Setiawan <>
>>> [EMAIL PROTECTED] 08/03/01 06:50 >>>
Can you insert an entire record into a table without having to specify each
individual field ??
I need to copy multiple rows in a table after I have modified its key. I do
not want to have to specify each field. Is there a way just to select the
record, modify the particular field, and then insert the record... WITHOUT
having to list all the fields or each individual value ??
Thanks
Kevin
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bambang Setiawan
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
