can't alter table modify

using sql 2000
i can do
alter table student rowid int identity but i can't do alter table student modify rowid int identity so i tried
alter table student alter column rowid int identity and that still doesn't work can some one let me know the right syntax? thanks.
if it is not possible to alter column to become identity then how can i add the rowid column as identity with the same orders as it was before. i know how to add the identity column but it just change my row orders around.
Who is Participating?
ralmadaConnect With a Mentor Commented:
Can you try this?

drop table #temp1
create table #temp1 (
col1 varchar(10),
col2 varchar(10),
rowd int identity
insert #temp1
select * from student

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
are there multiple rowids that are the same?  Try adding with no check to your statement.
StewSupportAuthor Commented:
this is what the table is like now
col1   col2      rowid
hell    heck      0
sure   no         0

in this scenario i want to modify rowid to identity column so that instead of 0 it will start from 1 and keep incrementing and can't be duplicate

second scenario is
col1   col2      
hell    heck      
sure   no        

then i want to add rowid as identity column but keep the row in the same order that i have before. the problem i run into now is everytime i add the rowid column, it keep messing up my row order. so instead of hell goes first it put sure as the first row with rowid = 1.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

You cannot use ALTER TABLE to change a column into IDENTITY column. 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 might also want to take a look at this article: 
Regarding you second scenario. I cannot replicate your situation in my enviroment, but I guess it has something to do with your collation. I'm using Latin1_General_CI_AS and it's working like a charm. The order is maintained.
StewSupportAuthor Commented:
well it doesn't for mine. and for scenario number 1 i know if i create a table definition it will work fine. but i dont want to and there's a reason for that.
Like I said, what collation are you using?
Also does your table has any indexes?
StewSupportAuthor Commented:
no its jsut a temp table
what i did was just do a select * into #temp1 from student then alter table #temp1 add rowid in identity. but it keeps changing the row order of the student id. so the row id never correct.
StewSupportAuthor Commented:
yes i know that will work. but i don't want that. lol. but if nothing else work i will have to do what you just said.
Not sure why you don't want to do this. Since I'm also creating a temp table.... Anyhow, this should do the trick.
StewSupportAuthor Commented:
tried to create new table but still got An explicit value for the identity column in table '#finaltemp' can only be specified when a column list is used and IDENTITY_INSERT is ON.
StewSupportAuthor Commented:
used SET IDENTITY_INSERT #finaltemp OFF still have the same error.
StewSupportAuthor Commented:
this is stupid. insert into #finaltemp select * from #temp1 has the error above but if i list all of the column name out like
insert into #finaltemp select col1,col2..... then that is fine. so stupid
Anthony PerkinsCommented:
It is not stupid.  That is by design.  And if you reat the following quote from the definition in BOL for INSERT it makes all the sense in the world:

When column_list does not specify all the columns in a table or view, either the default value, if a default is defined for the column, or NULL is inserted into any column that is not specified in the list. All columns that are not specified in the column list must either allow for null values or have a default value assigned.

INSERT statements do not specify values for the following types of columns because the SQL Server 2005 Database Engine generates the values for these columns:

Columns with an IDENTITY property that generates the values for the column.

Columns that have a default that uses the NEWID function to generate a unique GUID value.

Computed columns.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.