Home » SQL & PL/SQL » SQL & PL/SQL » adding column in the middle
adding column in the middle [message #278551] Mon, 05 November 2007 01:03 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I am creating a table which has many columns.In the table structure given there are some column name missing. I can skip those columns and add them later on .But this would add at the end of all the columns created during table creation.
I want to add them in accordance with the table structure.
How cani do so?

Alter table add column would add only at the end...

Thanks
Re: adding column in the middle [message #278554 is a reply to message #278551] Mon, 05 November 2007 01:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Actually it doesn't make any sense where the column is added in Database design. Only that matters is presence of column .

Anyways One option availble is ..

1. Create a different table in the order of columns you need with SQL 'Create table t1 as select x,y,... from t ;'

2. Drop old table

3. Rename New table to Old table name .

Drawback
This is not good practice to do this at Production DB
Constraints and indexes needs to re-create if any .

Thumbs Up
Rajuvan.

[Updated on: Mon, 05 November 2007 01:14]

Report message to a moderator

Re: adding column in the middle [message #278559 is a reply to message #278551] Mon, 05 November 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition 2 other ways:
- rename your table, create a view with previous table name and query the view
- use dbms_redefinition package

But column order is meaningless in relational model. Why do you want a special columns order?

Regards
Michel
Re: adding column in the middle [message #278563 is a reply to message #278551] Mon, 05 November 2007 01:23 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thats ok CTAS would work but my requirement is different.
For ex.
i have a table xyz with columns a,b,c,d.Now c is missing.
I created the table xyz with only three columnns a,b,d.
Later on i got the value for column c.
Now i want to add this column after b and before d ie in between b and d.

Is there any option in SQL to do so?

Re: adding column in the middle [message #278569 is a reply to message #278563] Mon, 05 November 2007 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Why do you want c between b and d?

Regards
Michel
Re: adding column in the middle [message #278572 is a reply to message #278563] Mon, 05 November 2007 01:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Read Carefully the answer before u post question again.

for my Solution :

CREATE TABLE ABC as SELECT A,B,'XXXXXXX'C, D from XYZ;
DROP TABLE XYZ ;
RENAME ABC to XYZ;


and ALTER TABLE XYZ MODIFY (C VARCHAR2(100))) .. Or any datatype as u need.

NOTE: It is Common misconception that Order of Column is important in DB design. It doesn't matter if the field 'c' is after 'b' or after 'e' .

Thumbs Up
Rajuvan.

[Updated on: Mon, 05 November 2007 01:35]

Report message to a moderator

Re: adding column in the middle [message #278576 is a reply to message #278551] Mon, 05 November 2007 01:38 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Dear Rajavu1,
I dont want to modify the datatype .Please try to understand.
Re: adding column in the middle [message #278584 is a reply to message #278576] Mon, 05 November 2007 01:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
varu123 wrote on Mon, 05 November 2007 08:38

Dear Rajavu1,
I dont want to modify the datatype .Please try to understand.

what part of "column order is irrelevant" is hard to understand? The order of columns does not matter in Oracle, or any relational database. It really doesn't matter. Try to understand that it is you - the user - that tells Oracle to either:
SELECT column_a, column_b FROM a_table

OR to
SELECT column_b, column_a FROM a_table


You can add a column in the end and just change your select order. If you fail to understand that and you really want to waste your time you can opt for any of the solutions hinted in the other replies (e.g. dbms_redefinition, ...).


And one last time: column order doesn't matter.

MHE
Re: adding column in the middle [message #278585 is a reply to message #278551] Mon, 05 November 2007 01:56 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I know that order does not matter and MAAher did a nice demonstration.

All i wanted to know was if there is any way to add columns any where in the table.

Thanks all for your time

[Updated on: Mon, 05 November 2007 02:10]

Report message to a moderator

Re: adding column in the middle [message #278587 is a reply to message #278585] Mon, 05 November 2007 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Why? Give me a single reason why column order should matter?

MHE
Re: adding column in the middle [message #278588 is a reply to message #278587] Mon, 05 November 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already asked him 3 times why he wants a special column order but did not get an answer. Not for the option I posted. It seems varu123 ignores me.

Regards
Michel

[Updated on: Mon, 05 November 2007 02:10]

Report message to a moderator

Re: adding column in the middle [message #278592 is a reply to message #278551] Mon, 05 November 2007 02:16 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I don't know why Michel thinks that every body wants to ignore him.
Re: adding column in the middle [message #278594 is a reply to message #278592] Mon, 05 November 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, I gave you 2 other ways to do it, I asked you 3 times the same question, you answered others not me so I estimate what is the more likely, and "ignore" is what is the more likely.

By the way, you still don't answer why you want a special order.

Regards
Michel

[Updated on: Mon, 05 November 2007 02:20]

Report message to a moderator

Re: adding column in the middle [message #278597 is a reply to message #278551] Mon, 05 November 2007 02:24 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I don't want a special order.
All i wanted is to know if there exists an option that could add the column anywhere in the table.
Re: adding column in the middle [message #278598 is a reply to message #278585] Mon, 05 November 2007 02:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
varu123 wrote on Mon, 05 November 2007 08:56

I know that order does not matter and MAAher did a nice demonstration.

All i wanted to know was if there is any way to add columns any where in the table.

Thanks all for your time

So it was an academic question? Well, in that case I think we've already answered that you could if you insisted but not in a single statement.

MHE
Re: adding column in the middle [message #278600 is a reply to message #278551] Mon, 05 November 2007 02:31 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Varu ,

It seems you are repeting the Question inspite of two or three answers . If you want the option to add a colum Anywhere "As you want" in a table , i dont think any RDBMS will provide a shortcut. At least oracle !!!! (Don't think that Oracle did not pay much attention to such kind of requirements . It is rather not needed for an RDBMS )

Have a nice search Smile

Thumbs Up
Rajuvan.

[Updated on: Mon, 05 November 2007 02:33]

Report message to a moderator

Previous Topic: Scheduled jobs not Running as per schedule
Next Topic: Sqlplus and the @ sign
Goto Forum:
  


Current Time: Sat Dec 03 18:21:50 CST 2016

Total time taken to generate the page: 0.10801 seconds