[sql] Adding an identity to an existing column

Basically there are four logical steps.

  1. Create a new Identity column. Turn on Insert Identity for this new column.

  2. Insert the data from the source column (the column you wished to convert to Identity) to this new column.

  3. Turn off the Insert Identity for the new column.

  4. Drop your source column & rename the new column to the name of the source column.

There may be some more complexities like working across multiple servers etc.

Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to alter-table

How to add a boolean datatype column to an existing table in sql? how to modify the size of a column MySQL: ALTER TABLE if column not exists Hive Alter table change Column Name Alter table to modify default value of column Rename column SQL Server 2008 How to delete a column from a table in MySQL Altering column size in SQL Server ALTER TABLE on dependent column ALTER TABLE to add a composite primary key

Examples related to identity-column

BULK INSERT with identity (auto-increment) column Inserting into Oracle and retrieving the generated sequence ID Adding an identity to an existing column How do you determine what SQL Tables have an identity column programmatically