How Order Column in a table with a script

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)

How Order Column in a table with a script

Author

Topic

dvinales
Starting Member

3 Posts

Posted - 2002-07-11 : 07:42:53

I have a table with 8 columns (Fields) and i need add 2 columns with a script, but in position 3 and 6.
When i use:
ALTER TABLE Clients ADD LastOrder smalldatetime NULL
GO
ALTER TABLE Clients ADD LastOrderAmount smallmoney NULL
GO
the columns are created after the last column.

Thanks!!

Danton

dvinales
Starting Member

3 Posts

Posted - 2002-07-11 : 07:44:21

In MS SQL Server 2000

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-11 : 07:56:13

ALTER TABLE ADD COLUMN appends columns to a table.
To do this in a script, you would need to :
1. Create a temp table with the layout you require (insert new fields)
CREATE TABLE dbo.tmpClients
(....LastOrder smalldatetime NULL... )
2. Insert the data into this new table
(watch out for IDENTITY_INSERT ) if you are using Identity columns
3. Script and drop the CONSTRAINTS on the old table
4. Drop the old table
5. Rename the New table to the old name (sp_rename)
6. Add the CONSTRAINTs, FOREIGN KEY to this table

OR

Perform the action in EM - table design and save the script that it generates. That's what I'd suggest.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-07-11 : 08:10:05

Is it really important where the columns are?

dvinales
Starting Member

3 Posts

Posted - 2002-07-11 : 18:21:54

Thanks YellowBug, but I knew this way, I imagined that maybe SQL2000 have a method more simple.
The column order is important only for good looking, I am working in outsourcing project, and good presentation speaks well about our work.

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:30:08

Use a VIEW. That would overcome that problem in a nutshell

Daniel Small MIAP
www.danielsmall.com IT Factoring

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources