How To Alter Identity Column

[youngun]

Quick question. How to make an existing column as identity column?

I have a table (A) containing duplicate records and duplicate unique key, and this unique key is an identity column (ok ok, don't ask me how I even start with this junk design... my ex-co-worker did this), I want to make it into a new table (B) without duplicate record but still retain the identity column property.

For example, table A contains:

Key Name SSN
------------------------------------
1 Bob 123456789
1 Bob 123456789
21 John 111222333
21 John 111222333


Here, Key column is an identity column.

I appreciate any help. Thanx in advance.

 

[saikrish]

Hi,
Well a simple solution should be have another table as the one you have discussed. Insert into this new table the distinct records from the original table and drop the original one. For eg:-

Create tableB(key numeric(5,0) identity,name char(25), ssn integer)
insert into tableB(name,ssn)
select distinct name,ssn
from tableA
(or)
Consider creating a clustered index on duplicate columns present in the original table. For eg:-

Create clustered index temp_index
on tableA (key, name, ssn) with ignore_dup_row

Vijay

 

[youngun]

Thanx Saikrish, but I think you overlooked an issue here. I want to retain the value of my KEY field and still make it an Identity column in the new table. If I create tableB (key numeric(5,0) identity), then I can't insert value into the KEY column because I will automatically assigned identity value from the system. The point here is to retain the value and still make it identity column.

Thanks anyway for your response.

 

[nbrk]

look at:

set identity_insert <table_name> on

It is in the Transact-SQL volume 1 manual

Good Luck

 

[saikrish]

Hi,
Ya as nbrk has said earlier, it is possible to insert values into an identity column with the identity setting referred. Probably the 'distinct' clause does not work with an identity column. So you could use a 'group by' on all the columns. For eg:-

set identity_insert tableB on
insert into tableB(key,name,ssn)
select key,name,ssn
from tableA
group by key,name,ssn

Note:- While using the 'set identity_insert' the columns need to be explicitly listed in the insert statement.

Vijay