CODEWITHSUNDEEP
sqlsql-serverms-access
iXDev
iXDev

Reputation: 35

Importing multiple access tables into one SQL server table

Ive been recently handed a very old access database to convert over to SQL server. It was done in the 90's by others but I have the .mdb files

The database is very old but has the customers actual formulation data that id like to keep. It is structured in separate tables as follows..

Tables

Formulas

Formula_000

Formula_001

Formula_002

As you can see above there is a header formula table with details such as name, date, version etc like so..

Table [Formulas] 

------------------------------------------------

| FORMULA NAME | ID | VERSION | CREATED |

| Formula_000 |  000   |     1     |  01/01/1997

| Formula_001 |  001   |     1     |  01/01/1997

------------------------------------------------

There is a separate accompanying table which holds the ingredients to go with each entry in the Formula table, so there is a table called [Formula_001] with the following columns inside..

Table [Formula_000] (x 100)

material code | % | 

material 1234 | 025 |    

material 5678 | 075 |  

------------------------------------------------

Id prefer not to have all these tables linked by a header table, but I need the data into a new structure. There are hundreds of these tables named Formula_xxx

What I want to do is loop through all the MS access tables in the above database except the header table [formulas] (i can delete this one to make it easier) , and copy them into a new master table, with an extra column with the formula number to identify which table it came from. Example below..

Table [Formula_Ingredients] 

material code | % | formulaID | 

material 1234 | 025 |    000

material 5678 | 075 |    000

material 1111 | 025 |    001

material 2222 | 075 |    001

------------------------------------------------

Any ideas guys?

Upvotes: 0

Views: 315

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

This is reminiscent of a recent task we carried out to import 11,000 Access databases into Postgres. You have multiple tables in one Access file instead of an identical table name in multiple Access files, but the same approach would apply.

We used a general-purpose SQL script processor (disclaimer: that I wrote). It's a Python program; you can get it from https://pypi.org/project/execsql/ or by running pip install execsql. The technique is to put the names of all of the formula tables into a table in the target database, then iterate through that using execsql's COPY metacommand to copy data from the Access database to SQL Server. This is illustrated in Example 13 of the documentation (http://execsql.readthedocs.io/en/latest/examples.html#example-13-import-all-the-csv-files-in-a-directory), though you would want to modify this to copy instead of import.

Upvotes: 1

Related Questions