If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Add Column Between Two Columns

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,458
The order of the columns should not matter. Just add it to the end.
Code:
alter table tablename add col3 varchar(something)
Reply With Quote
  #3 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #5 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Window Washer
 
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,373
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.
__________________
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

Last edited by Wim; 03-23-12 at 12:59.
Reply With Quote
  #9 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #10 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,774
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.
Reply With Quote
  #11 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
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
Reply With Quote
  #12 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #13 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,774
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.
Reply With Quote
  #14 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On