StewSupport
asked on
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.
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.
Kyle Abrahams, PMP
are there multiple rowids that are the same? Try adding with no check to your statement.
ASKER
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.
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.
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.
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:
http://www.sqlservercentral.com/articles/T-SQL/61979/
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.
http://www.sqlservercentral.com/articles/T-SQL/61979/
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.
ASKER
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
ASKER
used SET IDENTITY_INSERT #finaltemp OFF still have the same error.
ASKER
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
insert into #finaltemp select col1,col2..... then that is fine. so stupid
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:
<quote>
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.
</quote>
<quote>
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.
</quote>