Alter exisitng int column to identity in sybase

Question

Sybase 12.5 I have an existing table in production that needs it's PK int column to be altered such that it is auto populated - when the table was created it would ideally have had the ID column created as an Identity. This ID column is a foreign key in multiple other tables so deleting the table and starting again isn't an option. Problem is, I can't set the PK as an IDENTITY, and creating a temp column with the current values and copying these to a new IDENTITY column is also failing. As the ID column is already populated, I can't simply turn this column into an IDENTITY (at least, I haven't found any SQL that will do this). I have created a copy of the table, with an additional column holding the current PK value (division_id_tmp). ``` CREATE TABLE division_tmp ( division_id int IDENTITY NOT NULL division_id_tmp int, description varchar(255) NOT NULL, is_active tinyint DEFAULT 1 NOT NULL, ) ``` When i try to enter a datascript from the original table ``` INSERT INTO division_tmp (d…

Accepted Answer

Solved it. Think the underlying issue is I am a tool.

Getting confused between:

```
IDENTITY_INSERT 
```

and

```
IDENTITY_UPDATE
```

So, what I need to do is:

```
alter table division add division_id_tmp int IDENTITY not null 
SET IDENTITY_UPDATE division ON
update division set division_id_tmp = division_id
SET IDENTITY_UPDATE division OFF
alter table division drop division_id
EXEC sp_rename 
    @objname = 'division.division_id_tmp', 
    @newname = 'division_id', 
    @objtype = 'COLUMN'
```

Job done!

Alter exisitng int column to identity in sybase

Solution

Problem