Page 1 of 2 12 LastLast
Results 1 to 20 of 21

Thread: Insert new column in the middle of a table

  1. #1
    Geek_Guest
    Guest

    Insert new column in the middle of a table

    Question asked by visitor kiran

    Can I insert a new column in the middle of an existing table?
    Say I have created a table emp in order -> eno, ename, jobnow I want to insert a new column in between ename and job. If posble how?


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

    Re: Insert new column in the middle of a table

    In oracle, you can't do that. Between why do u need this? insert a new column in the middle of a table is not going to make any difference than appending a new column at the end.

    could you tell us why you need this?


  3. #3
    Moderator
    Join Date
    Sep 2006
    Answers
    920

    Re: Insert new column in the middle of a table

    You can add that in the end, but can view that in middle using sql select statement by changing the sequence of columns as per your requirement.

    Last edited by jainbrijesh; 04-23-2007 at 02:01 AM.
    Regards,
    Brijesh Jain
    ---------------------------------------------------------
    Connect with me on Skype: jainbrijesh
    Google Plus : jainbrijeshji

  4. #4
    Junior Member
    Join Date
    Feb 2007
    Answers
    17

    Re: Insert new column in the middle of a table

    You can't do it but what you can do it is add at the end of the table with alter table add column statement !!!


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

    Re: Insert new column in the middle of a table

    That is not supported in oracle ,at least till version 10.2.0.

    Actually it does not have any effect on data retrival and presentataion.


  6. #6
    Junior Member
    Join Date
    Jun 2007
    Answers
    18

    Re: Insert new column in the middle of a table

    No u cannot insert a new column in middle of table but to the last is possible with a alter command


  7. #7
    Junior Member
    Join Date
    Jun 2008
    Answers
    1

    Re: Insert new column in the middle of a table

    i too have the same problem can anybody sort it out


  8. #8
    Junior Member
    Join Date
    Jul 2008
    Answers
    1

    Re: Insert new column in the middle of a table

    You can add a new column in the end, not in the middle.
    inserting a new column in the middle of a table is not going to make any difference than appending a new column at the end.You can view that in middle using sql select statement by changing the sequence of columns as per your requirement.


  9. #9
    Contributing Member
    Join Date
    Sep 2007
    Answers
    35

    Re: Insert new column in the middle of a table

    you cannot insert column in the middle of the table.if u add any column to table it will appended to end.
    if u want to appear in the middle, create another table by selecting columns from that table.


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

    Re: Insert new column in the middle of a table

    Quote Originally Posted by kutty9585 View Post
    i too have the same problem can anybody sort it out
    Have you gone through the previous posts ?


  11. #11
    Junior Member
    Join Date
    Jun 2008
    Answers
    10

    Re: Insert new column in the middle of a table

    yeah sure u can insert a new column in the middle, unfortunately it cannot be done by script, you can do it manually.
    after you create the new column, open the design table, your column appears at the end, at the most left of the table, click on the column where an arrow appears, click on the arrow and place up the column where you want.
    hope it helps.


  12. #12
    Junior Member
    Join Date
    Jun 2008
    Answers
    1

    Re: Insert new column in the middle of a table

    U can't insert a new column in the middle of the table.
    Oracle by default, any new column that inserted be at the end of the table.


  13. #13
    Junior Member
    Join Date
    Mar 2008
    Answers
    3

    Re: Insert new column in the middle of a table

    the position of a column in an oracle table does not carry much value. The only problem is while selecting the columns with * the order will be not the desired one. however, this is till not solved by oracle.
    We have to bear with it or the other option is to write the comple name list in desired order while selecting.


  14. #14
    Junior Member
    Join Date
    Jul 2008
    Answers
    1

    Re: Insert new column in the middle of a table

    No we can not insert a new clumn in the middle of a table


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

    Re: Insert new column in the middle of a table

    Quote Originally Posted by ronaldrahme View Post
    yeah sure u can insert a new column in the middle, unfortunately it cannot be done by script, you can do it manually.
    after you create the new column, open the design table, your column appears at the end, at the most left of the table, click on the column where an arrow appears, click on the arrow and place up the column where you want.
    hope it helps.

    what design table you are talking about ?


  16. #16
    Contributing Member
    Join Date
    Apr 2008
    Answers
    31

    Re: Insert new column in the middle of a table

    It is not possible to insert a column in the middle of the table, But there is one way to do that in Oracle and SQL Server also. you can insert the column at the end and create a temp table in the reqired format and move the data from the original table to new table and replace the new file name with the old file name. By this way we can insert the column in the middle.
    Hope it will help for you.


  17. #17
    Junior Member
    Join Date
    Aug 2008
    Answers
    1

    Re: Insert new column in the middle of a table

    Yes, that is possible. Try with this

    1. Create a new table and copy the data across.

    SQL> RENAME tab1 TO tab1_old;
    Table renamed.

    SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;
    Table created

    Also please visit this site orafaq . com/wiki/SQL_FAQ


  18. #18
    Junior Member
    Join Date
    Jan 2008
    Answers
    24

    Re: Insert new column in the middle of a table

    That is not possible in oracle.But u can try the following

    Just u create a view on that particular table and keep the order of the columns in your view as you required.And use that view when ever you have to work on your table.Using view means You are indirectly working on ur original table.


  19. #19
    Junior Member
    Join Date
    Sep 2008
    Answers
    18

    Re: Insert new column in the middle of a table

    Oracle allow cols to be added only at the end of the existing table,bt some database allow cols to be added in middle of cols.for ex. In MYSQL following syntex is valid
    [alter table table_name add colname after colname;]


  20. #20
    Junior Member
    Join Date
    Sep 2008
    Answers
    1

    Re: Insert new column in the middle of a table

    No we can not insert a new clumn in the middle of a table in ORACLE.


Page 1 of 2 12 LastLast

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