0

I have old SQL sever table with 5000 rows. It has a column called OrderID which has the data type int. But this table doesn't have a primary key and OrderID is not on the sorted order. Can you please tell me how can I make this OrderID column the primary key and make it auto increment

D P.
  • 1,039
  • 7
  • 27
  • 56
  • You cannot set the autoincrement for existing column. It is possible only in Compact edition, afaik. – Roger Wolf Aug 13 '14 at 13:29
  • Check out this related question: http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 – Joseph B Aug 13 '14 at 13:30
  • `old SQL sever table...`. What is the version of your SQL Server database? – Joseph B Aug 13 '14 at 13:31
  • You want the auto-incremented values to start from the maximum value of orderID currently in the table? – Joseph B Aug 13 '14 at 13:40
  • What version of SQL Server are you using? If it's SQL 2012 or later, you could use a [SEQUENCE](http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx) (with some other steps) to accomplish your goal. Let me know if you need some further details. – Dave Mason Aug 13 '14 at 13:46

2 Answers2

0

You can't add identity to an existing column. Your best option is to create a new table with the same structure and an identity column, set identity_insert on and then copy your records from the old one into the new one.

Check out this answer from the MS SQL Forum

Ovidiu
  • 1,407
  • 12
  • 11
  • This is a good solution for a small table, like the table the OP is working with. Not so great for really large tables, though. I'd consider a SEQUENCE instead for SQL 2012 or later. If the SEQUENCE feature isn't avaliable, then perhaps a "temp" column (with the IDENTITY property) in the same table... – Dave Mason Aug 13 '14 at 13:51
  • After looking more closely at the link you provided, I see the "temp" column is mentioned there as option #2. That would get my vote. – Dave Mason Aug 13 '14 at 13:52
0

You can't add identity to existing column. Create a new column "new_OderId" , copy data from "OderId" column paste in "new_OderId" column.

#add new column to Order_table alter table Order_table add new_OderId int

#copy data from OrderId to new_OrderId update Order_table set new_OrderId=OderId

#drop OderId column alter table Order_table drop column OrderId

zeeshan12396
  • 382
  • 1
  • 8