Results 1 to 6 of 6

Thread: Inserting column in existing table

  1. #1
    Junior Member
    Join Date
    Mar 2006
    Answers
    1

    Inserting column in existing table

    How to insert a column inbetween two column in an existing table. Say, there r 5 columns in an table. If i want to insert a new column 'sal' as 3rd column. Wats the code?


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Inserting column in existing table

    physically (at memory level)you can't do it...you don't need to insert a column between two existing columns.


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Inserting column in existing table

    As per the rules of RDBMS physical position of data is not at all important.
    Data should be accessable irrespective of its position.

    Using oracle version available in the market till now it is not possible to do the same directly using only SQL commands.


  4. #4

    Re: Inserting column in existing table

    The replies u got r correct. u can't change the physical position of a column.


  5. #5
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Inserting column in existing table

    As all the two moderators said it's not important to do so.it matters only with view.but however if u want u can do so like ----> you can use

    dbms_redefination package .

    But you should have these previleges -->
    # execute access on the dbms_redefinition package
    # create any table # alter any table
    # drop any table
    # lock any table
    # select any table
    create table tab1(col1 char,col3 number) alter table tab1 add(col2 char);

    create table tab2 as select col1,col2,col3 from tab1; exec

    dbms_redefinition.can_redef_table('schema_name','tab1'); begin

    dbms_redefinition.start_redef_table('schema_name','tab1','tab2','col1,col2,col3');

    dbms_redefinition.finish_redef_table( 'schema_name', 'tab1', 'tab2' ); end;

    select *from tab1;
    col1 col2 col3
    ---------------


  6. #6
    Junior Member
    Join Date
    Aug 2010
    Answers
    2

    Re: Inserting column in existing table

    you can perform the addition of a new column between two columns by a stored procedure with outline:
    1. create table abc(col1 int , col2 int , col4 int)
    2. alter table abc add column (colmn3 int)
    3.create table abc_copy as select col1, col2, col3 , col4 from abc
    4.drop table abc
    5. rename abc_copy to abc


    it will surely work. But it is not a good practice to add columns inbetween as it takes time to be performed.you must spend sufficient amt of time to design the database that may not need such alteration after table creation..


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact