Re: Q:Duplicating rows with many colums

Hans Kandlbauer wrote:
>
> I want to duplicate a row of a table. No problem so far. But the table
> has about 80 colums and a primary key.
> Of course in the duplicated row I will use a differnet primary key.
> The only SQL statement I know for this purpose is an INSERT INTO ....
> SELECT FROM on.
> How can I copy the row, without naming each column in the SQL
> statement?
>
> I'm using Oracle Server 7.3 and want to put my statement in a stored
> PL/SQL procedure.
>
> Any good ideas?

If you want to insert all the columns, you have to list all the columns.

Unless...

  procedure InsertNew( OldKey ..., NewKey ...)   is
  begin
    delete tmpTable; -- This table must, of course, already exist

• or use dynamic SQL to create it here and drop it
• at the end of the procedure.

    insert into tmpTable

       select * from OriginalTable  -- transfer all 80 columns
       where key = OldKey;

    update tmpTable
    set key = NewKey; -- change just the column(s) needed

• where ... if needed. Not necessary in this example

    insert into OriginalTable

       select * from tmpTable; -- transfer all 80 columns back   end;

-- 
Tomm Carr
tommcatt@computer.org -- http://www.geocities.com/athens/delphi/6769
-- The Macintosh computer is like the open range;
-- In a world without fences, there are no Gates.