CODEWITHSUNDEEP
sql-server
Daniël Camps
Daniël Camps

Reputation: 1809

Building auto-increment in SQL Server on legacy data using varchar

Say I am redesigning a database with 5 columns, and someone before me thought it was a good idea to do the following:

Col1 Col2 Col3 Col4 Col5
------------------------
a    a    c    d    a_a_c_d
a    b    c    d    a_b_c_d
b    a    c    d    b_a_c_d
b    b    c    d    b_b_c_d
c    a    c    d    c_a_c_d
c    b    c    d    c_b_c_d

Where col5 is referenced as a foreign key by other tables.

In addition to creating a unique int ID per row, I want to remove Col4.

I cannot oversee the size of the refactor if I have to replace all references by my new unique ID. Theoretically, can I keep legacy references in tact, and automatically give new entries in Col5 a meaningless incrementing varchar, comparable to identity(1,1) (e.g. Foo1, Foo2, etc.)?

Upvotes: 1

Views: 86

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

because looks like you are going to get rid of Col5 anyway I would just create a new Col6 

ALTER TABLE [yourTable] ADD [Col6] INT IDENTITY(1,1)

And create a new after insert trigger to update Col5 with the new created value from Col6.

Upvotes: 2

Related Questions