add column to exiting table

I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeIDYou cannot add a column in the middle of a table. When you do an ALTER
TABLE to add a column, it is added at the and of the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeID|||Big D wrote:

> I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
that
> is between FIRSTNAME and TypeCodeID named Active? I am hoping not having
to
> drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
Hi,
Yes, you use ALTER TABLE to add a column to an existing table, but columns
do not have any order, just as you have no control over the order of rows.
You can specify any order you desire for columns when you retrieve rows with
a SELECT statement.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--|||Short answer, you shouldn't care. This:
> ID SSN LastName FirstName Active TypeCodeID
is equivalent to:
> ID SSN LastName FirstName TypeCodeID Active
It has the same data, and the same properties. When you query the data, you
order the columns how you want, as you should never do:
select *
from table
Other than when you are testing.
That having been said, I know what you mean and sometimes you want to
reorder the columns for "ease of use." For this, you have to drop and
recreate the table in the order you want the columns.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I did many times previously.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||However, Big didn't want to drop and re-create the table, which is exactly w
hat EM does...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Right" <serdar@.senkronyazilim.com> wrote in message
news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
> An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I
> did many times previously.
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message news:eXsabilYFHA.1868
@.TK2MSFTNGP14.phx.gbl...
>|||Thanks everyone who helped.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vF52XpYFHA.3616@.TK2MSFTNGP15.phx.gbl...
> However, Big didn't want to drop and re-create the table, which is exactly
> what EM does...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Right" <serdar@.senkronyazilim.com> wrote in message
> news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
>