what is the simple approach for transfering data from different designed databases?

[jcgldr]

hi everyone
i have a simple question, i have data in a old database and i created a new database completely differently designed and i need to transfer the old data into the new one, it is like 4 tables that each of them contains thousands of rows .
so what is the approach in basic concept that i need to do?

[jameslarsen]

If I understand your question properly, you can actually open both databases, highlight one of the tables in your old database, copy it (ctrl+c), then paste it into the new database. Microsoft Access allows copying and pasting of tables between databases. Hope that helps.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[jcgldr]

i can't copy the whole table because the table is also designed differently

[jameslarsen]

So you are trying to get data from an old table in an old database into a new table with a different structure in a new database? If so, you should copy the old table into the new database the way I described (as a temporary table), then use a query to translate the old data into the new table structure. For example, lets say you had an old customer table that had the first and last name separated, but in the new table you want them combined:

Old_Table
Customer_ID
Customer_FirstName
Customer_LastName

New_Table
Customer_ID
Customer_FullName

Create a query and execute it:
INSERT INTO tbl_New_Customers
SELECT tbl_Old_Customers.Customer_ID, tbl_Old_Customers.Customer_LastName + ', ' + tbl_Old_Customers.Customer_FirstName AS Customer_FullName
FROM tbl_Old_Customers;

Access will ask you if you want to append the rows into the new table, just tell it yes, and the old data should be copied over in the new format. Then you can just delete the old tables from the new database, since you don't need them anymore. I hope this helps.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com