ALTER TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ALTER TABLE

Author

Topic

ashy_16in
Starting Member

16 Posts

Posted - 2004-07-16 : 17:17:46

While using ALTER TABLE command in SQL to add a new column, it always creates the column at the end of the table schema. Is it possible to specify the exact position of the new column within the table schema without using Enterprise Manager ?. I am having some issues using Enterprise Manager since it copies all the records to a temp table first and this process takes a long time.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-07-16 : 17:48:48

You could do it in access.

Jim
Users <> Logic

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 03:37:20

If you add the new column at the "end" it is added without recreating the existing table.

Otherwise you have to recreate the table (which is what enterprise manager does behind the scenes - press the third button from letft in Design to see the script EM is going to use)

So, you could do something like:


CREATE TempTable
(
  MyCol1 SomeDataType,
  MyCol2 SomeDataType,
  MyNEWCol SomeDataType,
  MyCol3 SomeDataType,
  MyCol4 SomeDataType
)

INSERT INTO TempTable
(
  MyCol1, MyCol2, MyNEWCol, MyCol3, MyCol4
)
SELECT
  MyCol1, MyCol2, NULL, MyCol3, MyCol4
FROM MyOriginalTable
GO
DROP TABLE MyOriginalTable
GO
sp_rename 'TempTable', 'MyOriginalTable'

but I dread to think why the order of the columns in the table is important to you ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-17 : 09:25:02

Is this a duplicate post? I thinks it is.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37586

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources