|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Express - Identity specification
Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want
to change the column properites for [int] type of column, but I cann't change it. More in details... I'd like to set autoincremntal option and in order to change that I am trying to change Identity specification from no to yes, without success. It is primary key too, Name AricleID, int type, Primary set of binding --> ((0)) Full text specification --> No Identity specification --> No --> I want to change this Everything else is disabled Thanks Mike,
You cannot alter a table to change a column to use the IDENTITY property. What you will have to do is: CREATE TABLE v2_MyTable (AutoNum INT IDENTITY, OtherColumn NVARCHAR(100)) SET IDENTITY_INSERT v2_MyTable ON INSERT INTO v2MyTable SELECT * FROM MyTable SET IDENTITY_INSERT v2_MyTable OFF DROP TABLE MyTable EXEC sp_rename 'v2_MyTable', 'MyTable' Of course, this outline did not add constraints, indexes, and so forth, so be sure to readd those as well. RLF Show quoteHide quote "Mike" <ablyp***@yahoo.com> wrote in message news:4p1dk3lje3jv24n1sdk7kufctu86dpdk9u@4ax.com... > Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want > to change the column properites for [int] > type of column, but I cann't change it. > > More in details... > I'd like to set autoincremntal option and in order to change that I am > trying to change Identity specification from no to yes, without > success. > It is primary key too, > Name AricleID, > int type, > Primary set of binding --> ((0)) > Full text specification --> No > Identity specification --> No --> I want to change this > > Everything else is disabled > > Thanks > On Fri, 23 Nov 2007 09:33:14 -0500, "Russell Fields"
<russellfie***@nomail.com> wrote: Show quoteHide quote >Mike, > >You cannot alter a table to change a column to use the IDENTITY property. >What you will have to do is: > >CREATE TABLE v2_MyTable > (AutoNum INT IDENTITY, > OtherColumn NVARCHAR(100)) > >SET IDENTITY_INSERT v2_MyTable ON >INSERT INTO v2MyTable SELECT * FROM MyTable >SET IDENTITY_INSERT v2_MyTable OFF > >DROP TABLE MyTable >EXEC sp_rename 'v2_MyTable', 'MyTable' > >Of course, this outline did not add constraints, indexes, and so forth, so >be sure to readd those as well. > >RLF > > >"Mike" <ablyp***@yahoo.com> wrote in message >news:4p1dk3lje3jv24n1sdk7kufctu86dpdk9u@4ax.com... >> Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want >> to change the column properites for [int] >> type of column, but I cann't change it. >> >> More in details... >> I'd like to set autoincremntal option and in order to change that I am >> trying to change Identity specification from no to yes, without >> success. >> It is primary key too, >> Name AricleID, >> int type, >> Primary set of binding --> ((0)) >> Full text specification --> No >> Identity specification --> No --> I want to change this >> >> Everything else is disabled >> >> Thanks >> > Russel,
Does it means that I should create table from beggining. I really do not understand why I cann't change it within properties? Mike On Fri, 23 Nov 2007 09:33:14 -0500, "Russell Fields" <russellfie***@nomail.com> wrote: Show quoteHide quote >Mike, > >You cannot alter a table to change a column to use the IDENTITY property. >What you will have to do is: > >CREATE TABLE v2_MyTable > (AutoNum INT IDENTITY, > OtherColumn NVARCHAR(100)) > >SET IDENTITY_INSERT v2_MyTable ON >INSERT INTO v2MyTable SELECT * FROM MyTable >SET IDENTITY_INSERT v2_MyTable OFF > >DROP TABLE MyTable >EXEC sp_rename 'v2_MyTable', 'MyTable' > >Of course, this outline did not add constraints, indexes, and so forth, so >be sure to readd those as well. > >RLF > > >"Mike" <ablyp***@yahoo.com> wrote in message >news:4p1dk3lje3jv24n1sdk7kufctu86dpdk9u@4ax.com... >> Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want >> to change the column properites for [int] >> type of column, but I cann't change it. >> >> More in details... >> I'd like to set autoincremntal option and in order to change that I am >> trying to change Identity specification from no to yes, without >> success. >> It is primary key too, >> Name AricleID, >> int type, >> Primary set of binding --> ((0)) >> Full text specification --> No >> Identity specification --> No --> I want to change this >> >> Everything else is disabled >> >> Thanks >> > Mike,
Yes, it means recreating the table from the beginning. The identity property is not one that can be added and removed from a column (at least not up through SQL Server 2005), so the extra work is necessary. You can add a new column with the identity property, as such: alter table MyTable add NewIdentityColumn int identity However, this will not allow you to move any existing keys into the new column. It is automatically populated when it is created, so if you were trying to preserve existing values this will not work for you. If you are happy with the new ID values and are willing to make whatever changes your database needs to refer to these new values, then use this approach. RLF Show quoteHide quote "Mike" <ablyp***@yahoo.com> wrote in message news:du3gk393qkkdr0o9hdm8girvu5f36ocqmo@4ax.com... > Russel, > > Does it means that I should create table from beggining. > > I really do not understand why I cann't change it within properties? > > Mike > > On Fri, 23 Nov 2007 09:33:14 -0500, "Russell Fields" > <russellfie***@nomail.com> wrote: > >>Mike, >> >>You cannot alter a table to change a column to use the IDENTITY property. >>What you will have to do is: >> >>CREATE TABLE v2_MyTable >> (AutoNum INT IDENTITY, >> OtherColumn NVARCHAR(100)) >> >>SET IDENTITY_INSERT v2_MyTable ON >>INSERT INTO v2MyTable SELECT * FROM MyTable >>SET IDENTITY_INSERT v2_MyTable OFF >> >>DROP TABLE MyTable >>EXEC sp_rename 'v2_MyTable', 'MyTable' >> >>Of course, this outline did not add constraints, indexes, and so forth, so >>be sure to readd those as well. >> >>RLF >> >> >>"Mike" <ablyp***@yahoo.com> wrote in message >>news:4p1dk3lje3jv24n1sdk7kufctu86dpdk9u@4ax.com... >>> Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want >>> to change the column properites for [int] >>> type of column, but I cann't change it. >>> >>> More in details... >>> I'd like to set autoincremntal option and in order to change that I am >>> trying to change Identity specification from no to yes, without >>> success. >>> It is primary key too, >>> Name AricleID, >>> int type, >>> Primary set of binding --> ((0)) >>> Full text specification --> No >>> Identity specification --> No --> I want to change this >>> >>> Everything else is disabled >>> >>> Thanks >>> >>  
Other interesting topics
Copy views from one database server to another
Optimizing and shrinking large highly-transactional database Trigger to fire only after a commit. SQL server - cant see new backuped base Regarding Transaction Log HT sp_help_jobschedule results? Is the same? Logging from SP Why does MS SQL 2005 Management Studio runs/loads @ a snail pace? Setting up SQL Relationships? |
|||||||||||||||||||||||
