Import a .mdb to SQLServer via Stored Procedure
Question: **Context**

We need to import a .mdb archive to our local database so that we can
manipulate all the DATA.

**DATA**

that .mdb file Always have the same amount of tables (58) and the same table
structure, those tables may have 109.000 to 10million entries

**Actual situation**

Now we have a python program that do the migration (called Migrathon) that is
actually old and pretty damn slow, it takes more than 10 hours to import
16.000 entries to our local data base so here they wanted to change it.

**What i have to do**

First of all i work for ppl that use GeneXus Evo1, this tool can execute SP
from a datasource , so what i need or what i want to do is a Procedure that
can Take from the .mdb source an migrate every table that is into that file to
a local DataBase where i manipulate everything as i please

**My question**

Is there any chance of doing it? its SQLServer2008 and the Access Files are
from AC2003, the data structure as i said before are always the same
structure, same tables, same name the only diference are the amount of
entries, Thanks in advance


Answer: You can use and OLEDB driver into a t-sql procedure like this :

    
    
    SELECT * INTO #yourWorkTable FROM OPENDATASOURCE (‘Microsoft.Jet.OLEDB.4.0′, ‘Data Source=\\server-name\mdbs\test.mdb’)…[tableName]
    

With this query you got everything you need; just add some programming to
iterate through tables and you are done

