It is possible to alter existing MSSQL column to add auto increment option to this column (it is primary key, but not auto increment) without writing t-sql code, but just with alter stratements?
I can find solution with t-sql, but I need without it.
- Can I ask why you don't want to use T-SQL? If you have ways of solving a problem, why not use them?GarethD– GarethD2013-09-25 18:07:56 +00:00Commented Sep 25, 2013 at 18:07
- 5How are alter statements not T-SQL?Hart CO– Hart CO2013-09-25 18:15:44 +00:00Commented Sep 25, 2013 at 18:15
- when you say not using t-sql do you mean using sql-92? and no t-sql specific commands?user359135– user3591352013-09-25 18:32:19 +00:00Commented Sep 25, 2013 at 18:32
Add a comment |
1 Answer
according to this link http://social.msdn.microsoft.com/Forums/sqlserver/en-US/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032/how-to-alter-column-to-identity11 "You can't alter the existing columns for identity." It provides a couple of possible solutions. Relevant commands are:
alter table YourTable drop column OldId alter table YourTable add NewId int identity(1,1) 
2 Comments
Neville Nazerane
simple way to do the above suggested: go to the table on ms sql management studio. right click on the table > script table as > drop and create to > new query editor window edit and add the identity where needed and run the whole sql
Neville Nazerane
if you have data too, right click on your database in sql management studio > tasks > generate scripts. in choose object section, select "select specific database objects" and select your table(s) you want to alter and click next. on "set scripting options" section, choose "save to new query window" and click on advanced. in the advanced window, under "script drop and create", select "script drop and create" and under "types of data to script" select "Schema and data". click ok and proceed. you can then run the created queries.