Salesforce migration to SQL Server: SF's primary key is alpha numeric so how do I auto increment?

Question

I have moved the data from Salesforce to SQL Server to use with MS Access as the front end and realize that they use an alphanumeric ID for their ID columns.

How do I keep that data for table relationships and auto increment new IDs for new entries? Also what data type should I use?

Answer 1

This is too long for a comment.

You should invest the effort to change the tables so the primary keys are identity columns. You can keep the old primary key as an attribute, but you don't have to do anything to it.

Be sure all the foreign key relationships use the integer primary key. You can look up the new primary key value using the old value.

This will simplify your life in the future.

EDIT:

For example. If your existing table is A with two columns X and Y and X is the current primary key. Then:

create table new_a (
    a_id int identity primary key,
    x varchar(255) unique,
    y int
);

Insert into it:

insert into new_a (x, y)
    select x, y
    from a;

Then you have b, which is like:

create table new_b (
    b_id int identity(1, 1) primary key,
    b varchar(255) unique,
    a_id foreign key references a(a_id)
);

And the insert is:

insert into new_b (b, a_id)
    select b.b, a.a_id
    from b left join
         new_a
         on b.x = new_a.x;