RE: Inserting a Record into a Table

From: Kevin Lange <kgel_at_ppoone.com>
Date: Fri, 03 Aug 2001 17:11:52 -0700
Message-ID: <F001.0035F495.20010803065235@fatcity.com>

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 <>

>>> kgel_at_ppoone.com 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: kgel_at_ppoone.com

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: ListGuru_at_fatcity.com (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: Bambang.Setiawan_at_Sidola.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------