October 7, 2003 at 9:12 am
hello All
I have the table :[my_photogallery]
with the fields below:
[my_photogallery_id] [int] IDENTITY (1, 1) NOT NULL ,
[my_photogallery_cat_id] [int] NULL ,
[my_photogallery_title] [nvarchar] (150) NULL ,
[my_photogallery_image] [nvarchar] (250) NULL ,
[my_photogallery_bigimage] [nvarchar] (250) NULL ,
[my_photogallery_order] [int] NULL ,
[my_photogallery_articlelink] [nvarchar] (250)NULL ,
[my_photogallery_active] [bit] NULL ,
[my_photogallery_text] [ntext] NULL ,
[my_photogallery_appearInpopup] [bit] NULL
I like to add a new column, column_name =[my_photogallery_active] [bit] NULL
and place it at the 2nd position using the Alter or (any suggestions) command.
My table should look like ...
[my_photogallery_id]
[my_photogallery_active]
[my_photogallery_cat_id]
[my_photogallery_title]
[my_photogallery_image]
[my_photogallery_bigimage] [my_photogallery_order] [my_photogallery_articlelink]
[my_photogallery_text]
[my_photogallery_appearInpopup]
Thank you in advance...
October 7, 2003 at 9:19 am
Don't think you can do it with alter table command. You can use EM to insert new column at any position.
If you really want to place new column in specific position using T-SQL command, you have to recreate the table and reload the data.
Edited by - allen_cui on 10/07/2003 09:20:16 AM
October 7, 2003 at 9:23 am
Hi mirroras,
quote:
hello AllI have the table :[my_photogallery]
with the fields below:
[my_photogallery_id] [int] IDENTITY (1, 1) NOT NULL ,
[my_photogallery_cat_id] [int] NULL ,
[my_photogallery_title] [nvarchar] (150) NULL ,
[my_photogallery_image] [nvarchar] (250) NULL ,
[my_photogallery_bigimage] [nvarchar] (250) NULL ,
[my_photogallery_order] [int] NULL ,
[my_photogallery_articlelink] [nvarchar] (250)NULL ,
[my_photogallery_active] [bit] NULL ,
[my_photogallery_text] [ntext] NULL ,
[my_photogallery_appearInpopup] [bit] NULL
I like to add a new column, column_name =[my_photogallery_active] [bit] NULL
and place it at the 2nd position using the Alter or (any suggestions) command.
My table should look like ...
[my_photogallery_id]
[my_photogallery_active]
[my_photogallery_cat_id]
[my_photogallery_title]
[my_photogallery_image]
[my_photogallery_bigimage] [my_photogallery_order] [my_photogallery_articlelink]
[my_photogallery_text]
[my_photogallery_appearInpopup]
any specific reasons why this column must be exactly at this place???
You can place it anywhere you like and change your DML statements accordingly and be fine.
There is really no need for this!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 7, 2003 at 1:07 pm
You really can't do it without dropping the table and recreating it as Allen says. Even if you use Enterprise Manager, it will still drop the table and recreate it. EM just makes it simpler since it does everything for you and you don't need to figure out what steps to follow. You won't lose any data.
But as Frank asked: WHY?
-SQLBill
October 7, 2003 at 6:03 pm
As far as I know, you can't insert a column, you can only add them to the table, so what you do is rename the table, re-create the table with the new column in place, create your indexes, grant your permissions, and insert the columns from the renamed old table with a colum name list, taking special care with the identity columns.
-
Orion
EXAMPLE FOLLOWS:
sp_rename :[my_photo_gallery], :[my_photo_gallery_old]
create table :[my_photo_gallery]
(
[my_photogallery_id] [int] IDENTITY (1, 1) NOT NULL ,
[my_photogallery_active] [bit] NULL ,
[my_photogallery_cat_id] [int] NULL ,
[my_photogallery_title] [nvarchar] (150) NULL ,
[my_photogallery_image] [nvarchar] (250) NULL ,
[my_photogallery_bigimage] [nvarchar] (250) NULL ,
[my_photogallery_order] [int] NULL ,
[my_photogallery_articlelink] [nvarchar] (250)NULL ,
[my_photogallery_active] [bit] NULL ,
[my_photogallery_text] [ntext] NULL ,
[my_photogallery_appearInpopup] [bit] NULL
[my_photogallery_id] [int] IDENTITY (1, 1) NOT NULL ,
[my_photogallery_cat_id] [int] NULL ,
[my_photogallery_title] [nvarchar] (150) NULL ,
[my_photogallery_image] [nvarchar] (250) NULL ,
[my_photogallery_bigimage] [nvarchar] (250) NULL ,
[my_photogallery_order] [int] NULL ,
[my_photogallery_articlelink] [nvarchar] (250)NULL ,
[my_photogallery_active] [bit] NULL ,
[my_photogallery_text] [ntext] NULL ,
[my_photogallery_appearInpopup] [bit] NULL
)
insert :[my_photo_gallery]
(
[my_photogallery_id] ,
[my_photogallery_active] ,
[my_photogallery_cat_id] ,
[my_photogallery_title] ,
[my_photogallery_image] ,
[my_photogallery_bigimage] ,
[my_photogallery_order] ,
[my_photogallery_articlelink] ,
[my_photogallery_active] ,
[my_photogallery_text] ,
[my_photogallery_appearInpopup] ,
[my_photogallery_id] ,
[my_photogallery_cat_id] ,
[my_photogallery_title] ,
[my_photogallery_image] ,
[my_photogallery_bigimage] ,
[my_photogallery_order] ,
[my_photogallery_articlelink] ,
[my_photogallery_active] ,
[my_photogallery_text] ,
[my_photogallery_appearInpopup]
)
select
(
[my_photogallery_id] ,
NULL ,
[my_photogallery_cat_id] ,
[my_photogallery_title] ,
[my_photogallery_image] ,
[my_photogallery_bigimage] ,
[my_photogallery_order] ,
[my_photogallery_articlelink] ,
[my_photogallery_active] ,
[my_photogallery_text] ,
[my_photogallery_appearInpopup] ,
[my_photogallery_id] ,
[my_photogallery_cat_id] ,
[my_photogallery_title] ,
[my_photogallery_image] ,
[my_photogallery_bigimage] ,
[my_photogallery_order] ,
[my_photogallery_articlelink] ,
[my_photogallery_active] ,
[my_photogallery_text] ,
[my_photogallery_appearInpopup]
)
from :[my_photo_gallery_OLD]
NOTE!!: The IDENTITY_INSERT option must be set on the new table to bring the identity columns over from the existing table!!
Test this on a copy of your table before messing with live data.
October 7, 2003 at 8:24 pm
You're in a relational database - column order shouldn't matter.
But in order to do it, you need to recreate the table and populate it, as ovinson posted.
Careful with sp_rename, though - double check any FKs when you're done if it's a parent table.
Also, I'd avoid Enterprise Manager - especially if it's a large table. You'll lock up system tables while it's trying to add the column and rearrange all the data pages, etc.
October 8, 2003 at 7:18 am
You could use a view to order the columns however you want.
October 8, 2003 at 8:52 am
Unless you are sure that, you do not have any Sprocs or other code elements expecting the columsn in the previuos order of the table, you venture it. As mentioned by everyone else, you have ways of doing it if you absolutely need it by renaming or transfer and drop or if the table is small enough using EM.
Good Luck
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply