The goal is to merge the data of one database into another (of identical schemas), accounting for all the primary and foreign keys created by auto-incrementing identity columns.
The scope of this data migration covers five types of tables:
- Identity Primary Key. A single Id column that auto increments makes up the primary key.
- Composite Primary Key. The PK is a combination of multiple foreign keys.
- Heap. A table with no keys. Example might be a history or archive of another table.
- Temporal History. A type of HEAP, but with restrictions that require interacting with the temporal master table.
- PK without identity. Some tables have a PK but it's of a Code (varchar) or GUID, not managed by an auto-incrementing identity.
- python -m venv .venv/
- source .venv/bin/activate
- python -m pip install -r ./requirements.txt
- Note: on Apple Silicon use
brew install unixodbcandpip install --no-binary :all: pyodbc - Also [https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16#microsoft-odbc-18]
- Manually add tables in the tables.json config file into the appropriate waves
- Copy Data from Source DB into STAGE schema on Destination DB
- Add a new column onto every table in STAGE schema for New_PkId as well as each FK column
- Loop through each FK for table and update it's key based on the New_PkId value of referenced table
- Copy final values from STAGE schema into final table
- Data moves in waves. For example:
- Wave 1 contains tables with no FKs. Top of hierarchy.
- Wave 2 contains tables with FKs only to those tables in wave 1.
- Continue this pattern for all tables
- Loop through the following steps for each table in current wave
- Copy table from source to a staging area on destination DB
- Add a New_ column to the table with same data type as the original column
- Insert the records of stage table into destination table
- Update the stage table with the New_ values that were created during destination insert