**Situation**

I have 5 Access DB files, each one has 10 tables, 40 queries and 8 macros. All 5 Access DB files have same table name, table structure, same queries and same macros. The only **different is the data** contain in the table. If it matters, some tables on each database has rows between few hundreds to 100K+.

**What I am trying to achieve**

I am migrating these 5 Access DB files to single SQL Server (2008) database. ***Edit:*** After migrating, I do need to know which tables belong to which database since each original Access DB is associated with company's department so I need to keep track of this.

**My Solutions or Options**

Tables will be imported to SQL Server as tables. Queries will be imported as Stored Procedures. Macro will be imported as new Stored Procedures.

1. Import each Access DB's tables and queries to SQL Server DB and rename each tables and queries by giving them prefix to identify which tables belong to which database.
2. Same as #1, however, only import tables. As for the queries, only import one set of queries (40 queries) and modify them to *dynamically select, insert, update or delete* from the tables.
3. Import table A from 1st Access DB, table A from 2nd Access DB, table A from 3rd Access DB and so on, to one new table in SQL Server and give them unique identifier to identify which row of data belong to which database.

What do you think is the best approach? Please tell me if there is better way to do this than what I have listed. Thanks!