By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,143 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,143 IT Pros & Developers. It's quick & easy.

ALTER Table query

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Heist (ad*****************@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
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.
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.