|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ALTER TABLEI sometimes use the ALTER TABLe to add certain fields in my table. I need to
do it programatically. I will not get into why eventhough I have access to Enterprise manager and can use that to do it that way. My question is I'd like to know if there is syntax that I can use when I ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the table to place this new column. For example placing the new field in 3rd position or place in a table of 20 fields. I'd like to specify where in the table to place this new field... thanks in advance... AFAIK you can not
If you look in EM when you do it (save change script icon) you will see that EM creates a new table drops the old one and renames the newly created one to the original one http://sqlservercode.blogspot.com/ Show quote "Angel" wrote: > I sometimes use the ALTER TABLe to add certain fields in my table. I need to > do it programatically. I will not get into why eventhough I have access to > Enterprise manager and can use that to do it that way. > > My question is I'd like to know if there is syntax that I can use when I > ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the table > to place this new column. For example placing the new field in 3rd position > or place in a table of 20 fields. I'd like to specify where in the table to > place this new field... > > thanks in advance... And at the same time preserving the data?
Show quote "SQL" wrote: > AFAIK you can not > If you look in EM when you do it (save change script icon) you will see that > EM creates a new table drops the old one and renames the newly created one to > the original one > > http://sqlservercode.blogspot.com/ > > > "Angel" wrote: > > > I sometimes use the ALTER TABLe to add certain fields in my table. I need to > > do it programatically. I will not get into why eventhough I have access to > > Enterprise manager and can use that to do it that way. > > > > My question is I'd like to know if there is syntax that I can use when I > > ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the table > > to place this new column. For example placing the new field in 3rd position > > or place in a table of 20 fields. I'd like to specify where in the table to > > place this new field... > > > > thanks in advance... Well if you look at the change script you will see that the isolation level
is SERIALIZABLE This is the highest level, no updates,inserts or deletes can happen on this table while this script runs http://sqlservercode.blogspot.com/ Show quote "Angel" wrote: > And at the same time preserving the data? > > "SQL" wrote: > > > AFAIK you can not > > If you look in EM when you do it (save change script icon) you will see that > > EM creates a new table drops the old one and renames the newly created one to > > the original one > > > > http://sqlservercode.blogspot.com/ > > > > > > "Angel" wrote: > > > > > I sometimes use the ALTER TABLe to add certain fields in my table. I need to > > > do it programatically. I will not get into why eventhough I have access to > > > Enterprise manager and can use that to do it that way. > > > > > > My question is I'd like to know if there is syntax that I can use when I > > > ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the table > > > to place this new column. For example placing the new field in 3rd position > > > or place in a table of 20 fields. I'd like to specify where in the table to > > > place this new field... > > > > > > thanks in advance... Column order is completely irrelevant. If you need this for presentation
purposes, simply do it on the client. If you need this for documentation, use the INFORMATION_SCHEMA system views. ML > I'd Like to specify where in the table Sorry, you cannot do this with ALTER TABLE.> to place this new column. You can, of course, try to do what Enterprise Manager does: http://www.aspfaq.com/2528 Assuming you can get this to work, don't forget to use sp_refreshview on all
the views based on the table you alter. If you don't, your views may start returning unexpected results. Show quote "Angel" wrote: > I sometimes use the ALTER TABLe to add certain fields in my table. I need to > do it programatically. I will not get into why eventhough I have access to > Enterprise manager and can use that to do it that way. > > My question is I'd like to know if there is syntax that I can use when I > ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the table > to place this new column. For example placing the new field in 3rd position > or place in a table of 20 fields. I'd like to specify where in the table to > place this new field... > > thanks in advance... On Thu, 22 Sep 2005 09:37:07 -0700, mike wrote:
>Assuming you can get this to work, don't forget to use sp_refreshview on all Hi Mike,>the views based on the table you alter. If you don't, your views may start >returning unexpected results. AFAIK, that's only necessary for views defined as SELECT * FROM ... And since you shouldn't use SELECT * in production code anyway, there's no need to worry. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics