0

I'm in the process of migrating data to an SQL server database, ideally going foward I want to use the tables in this database to generate unique identities for the data being recorded in them, however the existing data comes with unique identities already.

I'd like to set up a table that looks like this

entry_num (PK) component (FK) long_id (Unique) - computed from combining row_num and component
1 THING1 THING1_1
2 THING2 THING2_2
3 THING1 THING2_3

I would like to be able to insert my existing data into the table by including it's existing id in the long_id column, and for future entries calculate the column automatically.

So my original inserted data might look like this:

entry_num (PK) component (FK) long_id (Unique) - computed from combining row_num and component
1 THING2 THING2_50
2 THING3 THING3_90
3 THING4 THING4_11

Alternatively could I manually specify the entry_num to match the identities?

I was planning on using

CREATE TABLE table_name (entry_num int IDENTITY...);

to auto increment this column, but is there another way that allows me to manually alter the identities of specific rows without violating the auto incrementing ability?

dsbbsd9
  • 107
  • 7
  • 1
    Basically you're asking to disregard the `IDENTITY` column during the migration and then have it back in play during production. Check out [this post](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) about how to add IDENTITY to an existing column. You really need to figure out the strategy for migrating the `IDENTITY` column. Most likely you want to not reuse the entry_num identifier; create a new `IDENTITY` column, move entry_num into a normal int column and use it as a reference to update your other DB tables to the new `IDENTITY`. – Tim Jarosz Nov 15 '22 at 16:26
  • I think you've hit the nail on the head there, I'm trying to have my cake and eat it too. I think I'm going to go with a similar strategy, have an auto-id column, and a legacy id column. with a computed column as the main unique id which references the legacy ID if it exists, but defaults to the auto ID if it doesn't. – dsbbsd9 Nov 16 '22 at 10:32

0 Answers0