DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Creating temp tables/duplicate tables

  1. #1
    Join Date
    Jun 2001
    Posts
    15

    Angry

    Here's the scenario:

    We have a script that basically will alter six tables in our dB. In order to keep the data intact, we have to, firstly, create six temp tables to store each of the affected tables data into. I think you can accomplish that with a select/like SQL statement, but I'm at a lose to what it is? Then, after the scripts execute, we'll copy the data, from the temp tables, back into the modified original tables. Finally, we'll drop the temp tables. What's the easiest way to code this?



    ĄThanks, in advance, for everyone's help!

  2. #2
    Join Date
    Apr 2001
    Posts
    107
    Hi,

    You can use the magical command:
    create table temp_table as select * from table;

    or:

    create table temp_table (field1, field2, etc...)
    as select field1,fiel2,etc...
    from table;

    hope it helps... (perform a test, I am not sure about integrity constraints...)

    Cheers

    Fabien

  3. #3
    Join Date
    Jun 2001
    Posts
    15

    Talking Magical Part

    I got the magical part figured out. That was the easiest part, :( . After we altered the existing tables, there was one or two columns added to each table. Now, when I do the insert into table1 (select * from table1_temp), it yells as me about this:

    SQLWKS> insert into wat_service_rmoc (select * from wat_service_rmoc_temp)
    2>
    insert into wat_service_rmoc (select * from wat_service_rmoc_temp)
    *
    ORA-00947: not enough values


    Any clues?


  4. #4
    Join Date
    Apr 2001
    Posts
    118
    You really need to explictly list all of the columns that are being selected from the temp table and whose values are being inserted back into the modified table. Some thing like

        insert into wat_service_rmoc( col1, col2, col3, col4 )
        select col1, col2, col3, col4 from wat_service_rmoc_temp;


    The above will only work if the newly added columns are either:

       -- NULL
       -- NOT NULL, but with a default value specified

    If neither of the two above conditions are true, then you will need to specify specific values for the two new columns in the select statement.

    HTH,

    Heath

  5. #5
    Join Date
    Feb 2001
    Posts
    123
    If you are basically adding columns to the tables, I fail to understand why you are copying/deleting/restoring the data to those tables. Why not just take a copy of the tables (for safety), make the modifications to add the columns, then - well - job done! The temp tables can be dropped because the data was not deleted from the originals.

    The only time that this could be a problem is if the columns being added are not null.

    HTH

    David.

  6. #6
    Join Date
    Jun 2001
    Posts
    15

    Thanks to Everyone

    After a couple hours of coding and figuring out things, I got everything figured. Thank you Heath and Dave_A for taking the time to help me out!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width