I'm using SQL Server 2012, I want to add an auto-increment to my primary key I don't know the exact syntax so i tried this but it obviously won't work !
Help please !!!
ALTER TABLE dbo.Equipe alter numero_equipe add IDENTITY(9999,1)
Asked
Active
Viewed 1,515 times
2
marc_s
- 732,580
- 175
- 1,330
- 1,459
white mind
- 45
- 6
-
1http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss – Christian Phillips Sep 10 '13 at 13:41
1 Answers
4
The way to solve syntax problems is not to shove characters around until it happens to work, but to read the (excellent) online docs. They specify syntax and give examples. Searching also helps to answer trivial questions.
You can perform the operation without touching table data using SWITCH: http://blogs.msdn.com/b/dfurman/archive/2010/04/20/adding-the-identity-property-to-a-column-of-an-existing-table.aspx
A connect item for a feature request exists: https://connect.microsoft.com/SQLServer/feedback/details/800127/allow-for-changes-to-the-identity-property-with-simple-syntax-and-metadata-operation-like-performance (please vote).
usr
- 168,620
- 35
- 240
- 369
-
In general I agree with you. How to alter an existing column `numero_equipe` to have the `IDENTITY` property is not well covered in the docs though. It requires a trick with `ALTER TABLE ... SWITCH` – Martin Smith Sep 10 '13 at 13:46
-
-
2@whitemind again you fail to search for "SQL Server alter identity with switch", but I added it to the answer for the benefit of future visitors. – usr Sep 10 '13 at 13:54