Home » SQL & PL/SQL » SQL & PL/SQL » How to duplicate Record
How to duplicate Record [message #278916] Tue, 06 November 2007 05:10 Go to next message
ShaShalini
Messages: 59
Registered: January 2007
Member
Hello

I have a table having more than 100 fields in the table. Now in my application I want to duplicate a particular record but with a different key.

E.g I have table wcp_ctx ( job_no , dec_date , dec_user .....);

I want to recreate the same record will all values same except the Jobno will be a newly generated one.

Any Idea how I can do it in SQL . As the application is being developped in Web using Java Tools.

Thanks

Re: How to duplicate Record [message #278917 is a reply to message #278916] Tue, 06 November 2007 05:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You will have to write out the statement; name all columns.
Re: How to duplicate Record [message #278918 is a reply to message #278916] Tue, 06 November 2007 05:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it in Pl/Sql by selecting the row into a %ROWTYPE, changing the primary key, and doing an INSERT using the modified %Rowtype variable.
Re: How to duplicate Record [message #278927 is a reply to message #278917] Tue, 06 November 2007 05:44 Go to previous messageGo to next message
ShaShalini
Messages: 59
Registered: January 2007
Member
I don't want to name all the columns .. Means if tommorow there are more fields added in the table then I need to modify my application.

I have been able to do this type of transaction on Forms 10G using Duplicate_record command

But I want to try the same thing in SQL


Thanks
Re: How to duplicate Record [message #278932 is a reply to message #278927] Tue, 06 November 2007 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could (and we're getting rapidly towards the bottom of the barrel here) create a Before Row Insert trigger on the table that would check to see if a specific Sys_Context had been set.
If not, it would do nothing. If it had been set, then it would overwrite the PK value of each inserted record with another value (you need to be able to auto-generate your PK values for this) and that way you'd get the record duplicated.
Re: How to duplicate Record [message #278940 is a reply to message #278927] Tue, 06 November 2007 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you "tomorrow" alter a table and add several columns into it, form you mentioned (along with the DUPLICATE_RECORD built-in) will also fail.

Why do you think that form will *know* the table was altered if you don't "modify your application" and add newly created columns as items into the forms' data block?
Re: How to duplicate Record [message #278957 is a reply to message #278932] Tue, 06 November 2007 07:45 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
JRowbottom wrote on Tue, 06 November 2007 13:09

You could (and we're getting rapidly towards the bottom of the barrel here) create a Before Row Insert trigger on the table that would check to see if a specific Sys_Context had been set.
If not, it would do nothing. If it had been set, then it would overwrite the PK value of each inserted record with another value (you need to be able to auto-generate your PK values for this) and that way you'd get the record duplicated.


I don't quite understand your theory: if you change the value of a column in a Before Row Insert trigger, wouldn't that value be used during the actual insert instead of "creating" a duplicate row?

Or am I missing something?
Re: How to duplicate Record [message #278968 is a reply to message #278957] Tue, 06 November 2007 08:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Once you've got this trigger in place, you set the sys_context that marks when you're duplicating rows, and then you can do
INSERT INTO TABLE nasty_workaround
SELECT * 
FROM   nasty_workaround
WHERE...
and it will (or at least should) duplicate the data in the rows, but give them new primary keys.
Re: How to duplicate Record [message #279082 is a reply to message #278916] Tue, 06 November 2007 20:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I don't think there is an easy way to do this without either writing out each column, or as JROW says, going to the bottom of the barrel.

Maybe you could give us some background as to why you want to duplicate these records, and how you were planning on generating a "different" key value? What is the key structure? Where would this code be executing from? What kind of volume are you looking for (rate of row duplication)?

I ask these questions because maybe your need to duplicate is at the core actually a design issue that can be addressed with some design change.

Kevin
Re: How to duplicate Record [message #279162 is a reply to message #279082] Wed, 07 November 2007 03:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think the rowtype approach is actually quite good for that, when we are not talking about millions of rows here.

I use it often when I have to copy data. For example I have a "document_config" table, and I use a procedure to create new document types based on old document types :

CREATE OR REPLACE PROCEDURE copy_document_config 
    (v_code_old  VARCHAR2, --document to copy from 
     v_code_new  VARCHAR2, --new document to create
     v_desc      VARCHAR2  --description of new document
    ) 
IS 

    v_doc  document_config%ROWTYPE;

BEGIN

   SELECT * INTO v_doc FROM document_config WHERE code = v_code_old;

   v_doc.code        := v_code_new;
   v_doc.description := v_desc;

   INSERT INTO document_config VALUES v_doc;

END;
/


Column document_config.code is the primary key on the table.




Re: How to duplicate Record [message #279239 is a reply to message #278916] Wed, 07 November 2007 08:04 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If you suddenly add new columns to a table, won't there have to be other application changes as well?

The data won't magically appear for the users to see or maintain.
Re: How to duplicate Record [message #279252 is a reply to message #279239] Wed, 07 November 2007 08:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, but then you just have to change the

- see data
- maintain data

program parts, otherwise you would have to change the

- see data
- maintain data
- copy data

program parts Very Happy

In my case I have a Java frontend in which I can edit the document_config table, which shows me all available columns of the table, so I don't even have to change the "see data" and "maintain data" program parts.

So when I need a new configuration setting for documents I just create a new column in the table, and I only need to change the part of the program that actually uses that configuration variable.

Re: How to duplicate Record [message #282454 is a reply to message #278916] Thu, 22 November 2007 00:55 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

This will work for OPs question, without changing any scripts for new column if added. This is same as Thomas query Smile
/* Formatted on 2007/11/22 12:18 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE emp_insert (
   emp_old   NUMBER,                                     --emp no to copy from
   emp_new   NUMBER                                        --new emp to create
)
IS
   emp_tab   emp%ROWTYPE;
BEGIN
   SELECT *
     INTO emp_tab
     FROM emp
    WHERE empno = emp_old;

   emp_tab.empno := emp_new;

   INSERT INTO emp
        VALUES emp_tab;
END;
/


[EDITED by LF: added [code] tags. Kiran, formatting the code is not enough if you don't include [code] tags. Check the OraFAQ Forum Guide to learn how to use them.]

[Updated on: Thu, 22 November 2007 15:21] by Moderator

Report message to a moderator

Re: How to duplicate Record [message #282743 is a reply to message #282454] Fri, 23 November 2007 04:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
kir_ait wrote on Thu, 22 November 2007 07:55

This is same as Thomas query Smile

Then what's the point in posting it?
Previous Topic: HELP TO BUILD A PL/SQL WITH THE FOLLOWING SQL
Next Topic: Procedure to return resultset
Goto Forum:
  


Current Time: Mon Dec 05 07:02:29 CST 2016

Total time taken to generate the page: 0.21776 seconds