Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find tha
t
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and clic
k
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [colu
mnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but thi
s
is approach is not optimal.
--
So Much - Yet - So LittleTry running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [co
lumnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the botto
m.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...[vbcol=seagreen]
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
>|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:

> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table
.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>
>|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Alter Table - Add Colum - Ordinal Position'
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==
>X-WBNR-Posting-Host: 207.59.213.130
>From: "examnotes" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position'
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
>
I[vbcol=seagreen]
droops[vbcol=seagreen]
statements[vbcol=seagreen]
Table.[vbcol=seagreen]
rights.[vbcol=seagreen]
find[vbcol=seagreen]
SQL[vbcol=seagreen]
syntax[vbcol=seagreen]
table.[vbcol=seagreen]
you[vbcol=seagreen]
and[vbcol=seagreen]
Is[vbcol=seagreen]
example, I[vbcol=seagreen]
and[vbcol=seagreen]
bottom.[vbcol=seagreen]
but[vbcol=seagreen]
>