Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    25

    Add Column Between Two Columns

    If i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,537
    The order of the columns should not matter. Just add it to the end.
    Code:
    alter table tablename add col3 varchar(something)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CREATE TABLE newTable (Col1 varchar(n),Col2 varchar(n),Col3 varchar(n),Col4 varchar(n),Col5 varchar(n));
    INSERT INTO newTable(Col1, Col2, Col4, Col5)
    SELECT Col1, Col2, Col4, Col5 FROM yourTable;
    EXEC sp_rename 'yourTable', 'yourTable_Old';
    EXEC sp_rename 'newTable', 'yourTable';

    You will have to worry about RI

    But Matt's right...it shouldn't matter where the column resides...(IT USED to in DB2)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    25
    Thanks Brett.

    I had a question on why the order shouldnt matter. Is it because you just change the order in the query?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    IF you use SELECT * a lot, then you probably would get burned

    You should NEVER use SELECT * except in some specific cases

    Reasons to not use SELECT * (and when to use it)

    The physical order of columns however should have no bearing on any development

    Most people would like the order to have meaning...and they usually do when you build out a logical data model. After that, all bets are off. Especially when you get into production with tons of RI and tons of data
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    25
    Makes sense. The person using the table uses select * most of the time. I needed to get the two table to match so he can just copy & paste the data together.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by justchillin View Post
    Makes sense. The person using the table uses select * most of the time. I needed to get the two table to match so he can just copy & paste the data together.
    You should let them suffer and write the code PROPERLY

    This is just one case...it will happen again, and again and again

    If he doesn't want to type, you can use the catalog to generate code

    TELL HIM TO FIX HIS GD CODE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,380
    Everywhere you can find supporters of the idea that it is important to write code that is easy for a human to read, yet in the case of a database that rule of thumb is never used.

    When I do a SELECT * FROM MyTable during development/monitoring/..., I expect to find e.g. the street name next to the house number and the date of birth close to the place of birth and country of birth.
    I know all this is irrelevant for the correct functioning of the database, but we should not make it hard for the humans who have to work with the tables we create to find the columns they are interested in.

    I think you can manually insert a column anywhere you want in an existing table when you do it manually within SSMS. I don't use the GUI to maintain the database, as it is not possible to reliably reproduce it in the development, staging and production environments.

    The other way is:
    - backup your table into a temp table
    - check if the temp table is an exact copy of your table
    - drop your table
    - create the table again with the extra column(s) in the right spot
    - copy back all the records from the temp table to your newly created table
    - check if the your new table is and exact copy of the temp table
    - don't forget all the other things that may surround the dropped and recreated table (FK's, indexes, views, ...)

    You could also insert the new column at the end of the table and create a view that places the newly added column nicely in the middle of the other columns. Let your user only use the view.

    INFORMIX supports this handy syntax: ALTER TABLE ADD COLUMN columnxxx dataType BEFORE columnyy.

    I only allow the use of SELECT * during development and ad hoc monitoring and querying, not in production code.
    Last edited by Wim; 03-23-12 at 13:59.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    INFORMIX supports this handy syntax: ALTER TABLE ADD COLUMN columnxxx dataType BEFORE columnyy.
    mysql supports this handy syntax: ALTER TABLE foo ADD COLUMN columnxxx dataType [ FIRST | AFTER columnyy ]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,947
    Both Wim and r937 have provided clear cases that show non-relational databases offer features that relational databases by definition can not provide. These features are very convenient for users that rely on predictable orders for rows and columns, but the features themselves are prohibited by relational theory.

    I understand that users want a nice, concrete order for things like rows and columns. From the UI (User Interface) perspective, those orders are very important. To preserve the mathematical fundamentals of relational theory and to allow vendors the opportunity to improve the database engine as time goes on, those "orders" need to be pushed to the front end application and must be kept "undefined" at the database engine level.

    For the intermediate or "power user" that wants more access than the existing applications provide, views offer a good compromise. The view can structure the data (rows and columns) to meet the user's needs and expectations without corrupting the underlying relational principles.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    Edit the table design in Management Studio, and then instead of saving the changes, click the button to script the changes out to a new query window.
    All the work of copying data to a temporary table, renaming tables, maintaining relational integrity, etc., is done for you.
    Run is as a script, because on large tables such changes may time out when running them through the GUI edit table wizard.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    ... without corrupting the underlying relational principles.
    dropping the table, and recreating it with the columns in the desired order, then, would be the way to proceed

    methinks thou didst jump the shark some time ago, pat

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,947
    Quote Originally Posted by r937 View Post
    methinks thou didst jump the shark some time ago, pat
    Sometimes it takes years to get people to learn, but repetition helps.

    Microsoft SQL is a relational product, and as a company they're pretty committed to keeping it that way. I don't see that changing, so adding a feature that breaks the relational model isn't very likely.

    Making design choices that depend on the order of rows and columns will break over time. The optimizer has been free to produce rows in any order it chooses since at least SQL 2000. As database engines become more sophisticated and use features like the column store in MS-SQL 2012, the order of columns can also vary within a table. There are a number of practical reasons above and beyond mathematical purity that forbid assumptions about the order of rows and columns within a table.

    A view is a better choice in my opinion than rebuilding the table for two reasons. A view will work no matter what happens to the data engine, so it is by definition more dependable than your suggested solution. A view is more efficient, because it doesn't require the data in the table to be unleaded and reloaded.

    I'll stay with simple tools that work for the foreseeable future!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    Microsoft SQL is a relational product...
    TL;DR

    fyi, it wasn't my suggestion, it was somebody else's, i was just commenting on how to achieve the original poster's requirement

    we are, after all, going to try to help people on this site, right? and not spout, er, i mean, explain theory like he-whose-name-shall-never-be-mentioned used to do...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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