How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre Ament
No, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament