SQL Server 2K inserting a new column

I am trying to positionally insert a new column into an existing table via a script I want to write. I know I can do this through the design mode in Enterprise manager, but how can I enter a column, say in position 6, of a current table, using Transact-SQL (e.g. alter table etc ...)

IS there a way to do this please ?

Thanks


DR

You could do it the way Enterprise manager does it in the background.

1. Copy the contents of the table to a work (or temp) table somewhere.
2. Drop the table.
3. Recreate the table with the new column positioned where you want it.
4. Copy the data back from the work table back into the table.
5. Drop the work table.

Hope this helps