I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of properly establishing relationships between tables, multiple fields and parameters are cramped in a single table.

I have broken up the table and plan to regroup the parameters into separate individual tables and link them together. My question is, the unorganized tables contain many rows of data, with over 40 different parameters, what is the best way to break up the table and still have all the data repopulated in the new tables linked?


I think your best bet is to do the restructuring in Access. After you're confident everything works, you can

  • create tables and constraints in PostgreSQL,
  • export tables from Access as CSV files, and
  • load the CSV files into PostgreSQL.

Everything works includes application code.

Make sure your Access database is backed up, copied, and not in use before you make structural changes.

To restructure tables in Access,

  • select distinct rows into a new table ("make table" query), or
  • create views (saved queries) that select the right rows and columns, or
  • copy a table, open it in design view, and delete irrelevant columns. (You'll probably want to delete some columns from the original table, too.)

I'm pretty sure Access lets you export the results of a query, so you can build queries that have the right structure, then export the queries.

  • Catcall thanks! What about the current data within existing tables. Should I create empty new tables first and then manually copy the data over or how do I do that? – alchuang Mar 13 '13 at 19:41
  • @alchuang: The first option above always works: "select distinct rows into a new table". That is, it always works on the data. It doesn't capture the constraints in the original base table. I prefer to create new, empty tables, then select distinct rows into them. (As an insert query, not as a "make table" query.) – Mike Sherrill 'Cat Recall' Mar 13 '13 at 20:27
  • Thanks! So by selecting distinct rows and inserting them the original constraints are kept within the new table? I also have a question regarding the second method - "create views (saved queries) that select the right rows and columns." How are queries different from tables in terms of data migration to PostGreSQL? Are exported tables and exported queries both as effective? Are there advantages of one over the other? – alchuang Mar 14 '13 at 14:32
  • Inserting distinct rows has no effect on the structure of tables. That includes column definitions, foreign key constraints, other constraints, etc. Queries and tables are the same as far as exporting data to CSV or tab-delimited files. Queries might have a slight advantage; exporting a query gives you the most up-to-date data. But queries are based on the old base tables. If the constraints in those tables let bad data in, you'll get bad data in the queries. So you'll export bad data from the queries. – Mike Sherrill 'Cat Recall' Mar 14 '13 at 14:42
  • Thank you for the prompt reply! Does Appending do the same as an insert query in Access? If so, I think I'll be going that route. Creating empty tables with defined fields and constraints and then querying the data from the old database and appending them over to the new fields. Does that sound like the right way to do this? – alchuang Mar 14 '13 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.