Friday, February 10, 2012

Changing data type

What's the best way to change numeric column to Identity column?
Table: Orders
Field: OrderID (numeric) must be change to Identity
Records: 120,000
Thanks,
WalterTo change it programmatically you have to create a new table and then
insert the existing data. If you make the change in the Enterprise
Manager interface it will automatically generate the script for you.
For 120K rows it should be feasible to recreate the table that way.
David Portas
SQL Server MVP
--|||Walter,
There is not an easy way to do this,. SQL Server does not let you add the
IDENTITY property to an existing column, so you have to create a temporary
table, add existing rows, drop foreign key constraints referencing the old
column, rename the table, create fk constraints again, etc. Do it using EM,
while in design mode, change the identity property of the column and click
"save change script" button (third from left to right in the tool bar) and
you will see the script.
AMB
"Walter" wrote:

> What's the best way to change numeric column to Identity column?
>
> Table: Orders
> Field: OrderID (numeric) must be change to Identity
> Records: 120,000
>
> Thanks,
> Walter
>
>

No comments:

Post a Comment