Twelve tables, one collection

DBToMongo » Use cases » Combine split tables

Download DBToMongo Free Trial »     Buy DBToMongo Now »

The situation

The relational database grew one year at a time, or one region at a time: orders_2019, orders_2020, orders_eu, orders_us - the same columns, over and over, because that was practical at the time. In MongoDB the reason for the split disappears. What you want is one orders collection.

Copying them one by one and merging afterwards means loading the same collection a dozen times, and a document has no memory of which table it came from.

What you do

1. Open Similar Tables to One Collection. DBToMongo looks at the source and finds the tables that share the same columns.
2. Tick the ones to combine; set the single destination collection.
3. Check the mapping once - it applies to all of the selected tables, which is the point.
4. Run. Every selected table is appended into that one collection in a single pass.

If the documents need to say where they came from, add that as a field on the destination and map the source table name into it - a small step that saves a lot of guessing later. Sources of different engines can be combined too: a MySQL branch and a SQL Server branch can land in the same collection.

What it will not do

• It does not reconcile tables that genuinely differ. The tables have to be similar - same columns to map. Where two "orders" tables really have different shapes, the mapping will not merge them sensibly.
• It does not de-duplicate. If the same row exists in orders_2019 and orders_2020, both documents end up in the collection. Cleaning that up is a separate job.
• It does not add a source column for you. If you want to know which table a document came from, that field has to be part of the destination and the mapping.
• It does not design the target documents. The result is the union of the source rows as documents - not a summarised or nested model.

Related

The mode itself: similar tables to one collection. A whole database instead: migrate a database. One to one: table to collection. Adding more later without duplicates: keeping it in sync.