MSSQL Data Migration

Merges the data of one database into another (of identical schemas), preserving primary and foreign key integrity across auto-incrementing identity columns.

The scope of this data migration covers five types of tables:

1. Identity Primary Key - A single Id column that auto increments makes up the primary key.
2. Composite Primary Key - The PK is a combination of multiple foreign keys.
3. Heap - A table with no keys. Example might be a history or archive of another table.
4. Temporal History - A type of HEAP, but with restrictions that require interacting with the temporal master table.
5. PK without identity - Some tables have a PK but it's of a Code (varchar) or GUID, not managed by an auto-incrementing identity.

Local Env Setup

Requires Python 3.14+ and uv.

git clone https://github.com/nathanthorell/mssql-data-migration.git
cd mssql-data-migration
uv sync --extra dev

• Note: on Apple Silicon use brew install unixodbc and pip install --no-binary :all: pyodbc
• Also install-microsoft-odbc-driver-sql-server-macos

Environment Variables

Copy .env.example to .env and update the connection strings for source and destination:

cp .env.example .env

Connection string format: Server=host,port;Database=db;UID=user;PWD=pass

Running

From the repo root (it looks for tables.json in the current directory):

uv run data-migration

Goals and Assumptions

1. Manually add tables in the tables.json config file into the appropriate waves
2. Copy Data from Source DB into STAGE schema on Destination DB
3. Add a new column onto every table in STAGE schema for New_PkId as well as each FK column
4. Loop through each FK for table and update it's key based on the New_PkId value of referenced table
5. Copy final values from STAGE schema into final table

Notes

• 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