Transferring data from MS Access to SQL Server-CodePudding

I have MS Access tables that are indexed but accept duplicates. Tables have been transferred to SQL Server and linked to Access.

How do you replicate primary keys that accept duplicates?

CodePudding user response：

You should consider using SSMA Sql Server Migration Assistant for Access

It can move up tables.
It will move up related data, setup PK columns for you.
It will maintain, and create relationships for you.
It will maintain and create all indexes you have now.

If you just have a few tables, say 2-5 tables? Then sure, just import them and setup the relatonships and indexs your self.

However, the last few migrations of data from Access to SQL server? There was in excess of 80 tables - and HUGE numbers of reatonships, indexes, and of course PK settings. The migration wizard thus can send up all of the tables - and setup all of the tables correctly on SQL server for you. Including PK values, FK values (Foreign Keys (related tables)), and even constraints are in most cases correctly move up to sql server.

what is nice, is then you can re-link the tables in your Access application, and you now using SQL server for the back end database.

SSMAA can be found here:

https://www.microsoft.com/en-us/download/details.aspx?id=54255

CodePudding user response：

I think you're confusing an "index" with a "primary key".

An "index" is a structure that helps optimise queries. Indexes don't have to be unique. A "primary key" is a logical constraint on a column which requires that all values in the column are unique.

It sounds like what you want to do is import the data into SQL and create an index to help speed up queries, but where that index is not constrained to be unique.

Here's the syntax to do that. Suppose we have some table T:

create table T(i int, j int, k int);

We want to create an index on column i to speed up queries, but i is not unique. To do that we create a regular (non unique) index:

create index MyIndexName on T(i);

As a rule, I tend to name my indexes based on what they are indexing. So in the above case I wouldn't call the index "MyIndexName", I would call it something like ix_T_i.