ALTER Table query

Hi, I just want to know to turn this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]
into this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] IDENTITY (1, 1) NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]

using an ALTER TABLE query. I tried using:
ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
(1, 1) NOT NULL but it's not working. Anyone has any idea how I could
do it? Thanks.Heist (advertiseallyouwant@.hotmail.com) writes:
> Hi, I just want to know to turn this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> into this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] IDENTITY (1, 1) NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> using an ALTER TABLE query. I tried using:
> ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
> (1, 1) NOT NULL but it's not working. Anyone has any idea how I could
> do it? Thanks.

You cannot use ALTER TABLE to change a column into IDENTITY column
(except on SQL Server CE!). One way is to rename the table, create
a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.

You can also do it in Enterprise Mangager - which will renamed and
move data behind the scenes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What I ended up doing is using SQL Server Entreprise Manager to
"manually" alter the table and then I used the script generator to
create a script I could then used. Thanks.sql