Are there any best practices someone can share with me about integrating old and new data sources together on a SQL server?

To clarify, my company receives information from vendor A but we are transitioning to vendor B. Both vendors provide us similar customer and transaction data (e.g.: account numbers, addresses, etc.) but the schema and data depth and breadth between vendors are very different. Also, key items like account number between vendor A and B are not the same; all customers will receive a new account number. I need to be able to bridge historical and current/future results together.

A couple options that come to mind are...

1. Create a set of SQL views that reformat the data between tables for vendors A and B into a similar format (even though their source schema is different).

2. Treat data from vendor A and B purely as staging table data--and create a new set of tables where I can combine data from vendor A and B where the two sources have shared columns. That way, if vendor C ever came along, the data used in the SQL server would be in the same format and using the same column names.

Both of these options seem messy and with their pros/cons. Not sure if there's another choice out there... or if people have come across this decision point themselves.

Any help is appreciated. Thank you!

CodePudding user response：

This

Treat data from vendor A and B purely as staging table data--and create a new set of tables where I can combine data

is the general and most common approach.