Home » SQL & PL/SQL » SQL & PL/SQL » copy a row and insert into same table (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi , os unix)
copy a row and insert into same table [message #434159] Mon, 07 December 2009 09:15 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have one main table and several children tables ,
first I want to copy a row from main table insert into same table with a different Pk and do the same for all children tables, Please tell me if there are any shortcuts for this , rather tan me script for this ?

Re: copy a row and insert into same table [message #434161 is a reply to message #434159] Mon, 07 December 2009 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do using single INSERT statement

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: copy a row and insert into same table [message #434174 is a reply to message #434159] Mon, 07 December 2009 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no insert/duplicate and change pk cascade statement.
You have to define and do it by yourself in a procedure or script.
It is not difficult to generate such a procedure or script (if you have defined FK in the database).

Regards
Michel
Re: copy a row and insert into same table [message #434187 is a reply to message #434174] Mon, 07 December 2009 11:19 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I am trying this but I get error donot know what is wrong

declare
v_sys_audit_prog_id number;
v_number number;
begin
v_sys_audit_prog_id:=853;
select   ea_audit_prog_seq.nextval  into v_number from dual ;
create table ea_audit_prog_temp as (select * from ea_audit_program where sys_audit_prog_id=853);
update  ea_audit_prog_temp  set sys_audit_prog_id=v_number;
insert into ea_audit_program(select * from ea_audit_program_temp  where sys_audit_prog_id=v_number );
drop table ea_audit_prog_temp;
end;


here the error message
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
   begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << close current delete fetch lock insert
   open rollback savepoint set sql execute commit forall merge
   pipe


please explain me what is wrong

[Updated on: Mon, 07 December 2009 11:20]

Report message to a moderator

Re: copy a row and insert into same table [message #434188 is a reply to message #434187] Mon, 07 December 2009 11:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Inside PL/SQL DDL is not allowed.
You really should NOT! do DDL inside PL/SQL, but if you insist it can be done (ab)using EXECUTE IMMEDIATE
Re: copy a row and insert into same table [message #434189 is a reply to message #434188] Mon, 07 December 2009 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To add an image to what BlackSwan said (and it is the EXACT comparison): do you generate and compile new code for your application in your application?
DDL are part of the code, they must be executed (code compiled and linked in program terms) before you use them not during the execution of another part of the application.

Regards
Michel
Re: copy a row and insert into same table [message #434190 is a reply to message #434187] Mon, 07 December 2009 11:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also your example really doesn't need the temporary table. What you are doing can be done with a single insert select.
Re: copy a row and insert into same table [message #434191 is a reply to message #434190] Mon, 07 December 2009 11:33 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
but my tables has like 50 columns I cannot write insert , and select for all 50 columns , is there any simple way I am missing ?
Re: copy a row and insert into same table [message #434192 is a reply to message #434191] Mon, 07 December 2009 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post DDL for your tables.

see URL below to easily extract table DDL

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Re: copy a row and insert into same table [message #434195 is a reply to message #434191] Mon, 07 December 2009 11:48 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
50 columns isn't a lot in the grand scheme of things. I've got tables with > 150.

As black swan suggests pull the ddl and do some quick editing to get a list you can use.
Re: copy a row and insert into same table [message #434196 is a reply to message #434195] Mon, 07 December 2009 11:58 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I pulled script using toad , I am doing it from a simple table not the table from 50 columns , but I have many such tables , to copy data

CREATE TABLE EA_AUDIT_PROGRAM
(
  SYS_AUDIT_PROG_ID    NUMBER                   NOT NULL,
  SYS_AUDIT_ID         NUMBER                   NOT NULL,
  SYS_PROG_ID          NUMBER                   NOT NULL,
  OBJ_VERSION          NUMBER,
  GO_USER_ID           NUMBER,
  OCD_DUE_DATE         DATE,
  ASSIGNMENT_MEMO      BLOB,
  MEMO_CREATED         CHAR(1 BYTE),
  ACD_SENT_TO_DFI      DATE,
  ADL_SENT_TO_GRANTEE  DATE,
  ACD_SENT_TO_OIG      DATE,
  NA_SENT_TO_PSC       DATE,
  PD_USER_ID           NUMBER,
  IS_LIVE              CHAR(1 BYTE)             NOT NULL
)

[Updated on: Mon, 07 December 2009 11:58]

Report message to a moderator

Re: copy a row and insert into same table [message #434197 is a reply to message #434159] Mon, 07 December 2009 12:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then use sql to generate sql. Query user_tab_columns to get the column list for each relevant table and then use some sql and / or pl/sql to output an insert statement for each table.
Copy end result into a procedure so you're not running dynamic sql.
Job done.
Re: copy a row and insert into same table [message #434198 is a reply to message #434197] Mon, 07 December 2009 12:07 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
but the problem with this is anytime I change a column name I have to change the script , is there any way I can avoid column names ?
Re: copy a row and insert into same table [message #434199 is a reply to message #434198] Mon, 07 December 2009 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but the problem with this is anytime I change a column name I have to change the script

1/ And then? How often do you change the code? What's the problem of changing one part of the code when you change another part, anyway you have to review it and test it to see if there is no change to make

2/ This is wrong if you use user_tab_columns and so to generate the script. You deliver new code, you deliver new scripts as well. Scripts are part of the code.

Regards
Michel
Re: copy a row and insert into same table [message #434200 is a reply to message #434198] Mon, 07 December 2009 12:24 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
This of course would lead to the question, "Why would you change a column name?"
Tables should be static. Rarely will you ever add a column and I have never needed to change a column name.
Re: copy a row and insert into same table [message #434201 is a reply to message #434198] Mon, 07 December 2009 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
DECLARE
  v_sys_audit_prog_id  NUMBER;
  v_number             NUMBER;
BEGIN
  v_sys_audit_prog_id := 853;
  
  SELECT ea_audit_prog_seq.nextval
  INTO   v_number
  FROM   dual;
  
  INSERT INTO ea_audit_program
  (SELECT v_number,
          sys_audit_id,
          sys_prog_id,
          obj_version,
          go_user_id,
          ocd_due_date,
          assignment_memo,
          memo_created,
          acd_sent_to_dfi,
          adl_sent_to_grantee,
          acd_sent_to_oig,
          na_sent_to_psc,
          pd_user_id,
          is_live
   FROM   ea_audit_program
   WHERE  sys_audit_prog_id = v_number);
END; 

[Updated on: Mon, 07 December 2009 12:33]

Report message to a moderator

Re: copy a row and insert into same table [message #434217 is a reply to message #434201] Mon, 07 December 2009 16:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is also the option of using a ROWTYPE and a cursor loop to copy a row:

begin
  for l in (
     select * from ea_audit_program 
      where sys_audit_prog_id = 853
  ) loop

    SELECT ea_audit_prog_seq.nextval
     INTO   l.sys_audit_prog_id
     FROM   dual;
     
     insert into ea_audit_program values l;

  end loop;
end;


That way you don't have to change the code when you add column to the table, you only have to change it if there are additional columns you want to adjust before you insert the new row.
Re: copy a row and insert into same table [message #434218 is a reply to message #434217] Mon, 07 December 2009 16:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>There is also the option of using a ROWTYPE and a cursor loop to copy a row:
How to use ROWTYPE & INSERT a different SYS_AUDIT_PROG_ID value?
Re: copy a row and insert into same table [message #434219 is a reply to message #434218] Mon, 07 December 2009 16:32 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
BlackSwan wrote on Mon, 07 December 2009 23:14
>There is also the option of using a ROWTYPE and a cursor loop to copy a row:
How to use ROWTYPE & INSERT a different SYS_AUDIT_PROG_ID value?


By changing the SYS_AUDIT_PROG_ID in the rowtype between the select and the insert, like I did in my example.
Previous Topic: If Statement / Variable as an SQL Column name
Next Topic: Getting session tree lock
Goto Forum:
  


Current Time: Fri Dec 09 06:14:02 CST 2016

Total time taken to generate the page: 0.18005 seconds