Printable View
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
Arman.... The following is from BOL.
Arguments
table
Is the name of the table to be altered. If the table is not in the current database or owned by the current user, the database and owner can be explicitly specified.
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.
The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.
column_name
Is the name of the column to be altered, added, or dropped. For new columns, column_name can be omitted for columns created with a timestamp data type. The name timestamp is used if no column_name is specified for a timestamp data type column.
------------
Arman at 12/17/2001 5:25:39 PM
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
Thanks Jeremy. It seems my column is NOT in the "CANNOT ALTER" list.
I did this test:
create table Test (ID int not null, Name varchar(20))
alter table test clter column ID int indentity(1, 1) not null
and it failed with the same erro message. Am I missing someting?
Thanks.
------------
Jeremy at 12/17/2001 5:35:57 PM
Arman.... The following is from BOL.
Arguments
table
Is the name of the table to be altered. If the table is not in the current database or owned by the current user, the database and owner can be explicitly specified.
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.
The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.
column_name
Is the name of the column to be altered, added, or dropped. For new columns, column_name can be omitted for columns created with a timestamp data type. The name timestamp is used if no column_name is specified for a timestamp data type column.
------------
Arman at 12/17/2001 5:25:39 PM
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
Arman... I believe that an identity column is a computed column... as such, you can't modify the existing column to a computed column. I would use this, "ALTER TABLE test
ALTER COLUMN name int NOT NULL" and then you should be able to go into enterprise manager, and simply choose it as an option within the design view of the table... make sure that there is no data in the column first... That should work.
Jeremy
------------
Arman at 12/17/2001 5:58:07 PM
Thanks Jeremy. It seems my column is NOT in the "CANNOT ALTER" list.
I did this test:
create table Test (ID int not null, Name varchar(20))
alter table test clter column ID int indentity(1, 1) not null
and it failed with the same erro message. Am I missing someting?
Thanks.
------------
Jeremy at 12/17/2001 5:35:57 PM
Arman.... The following is from BOL.
Arguments
table
Is the name of the table to be altered. If the table is not in the current database or owned by the current user, the database and owner can be explicitly specified.
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.
The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.
column_name
Is the name of the column to be altered, added, or dropped. For new columns, column_name can be omitted for columns created with a timestamp data type. The name timestamp is used if no column_name is specified for a timestamp data type column.
------------
Arman at 12/17/2001 5:25:39 PM
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
You don't have to have an empty column to turn it into an Identity column in SQL 2005. I just migrated a table from one server to another that had an identity column with gaps in the data. To do the migration, the target column was not an Identity column. Afterwards, I just used the Design view in the SSMS gui to turn on the Identity property for the column. It worked! It even started incrementing at the right number.