ALTER TABLE to add a column between other columns

I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>