I have two almost identical (structurally) databases:
- Documents
- DocumentsOld
The original database existed before table partitioning was available, so the solution they found was to create the second database and migrate all the old data to it after each year. Now they want to merge both databases again.
I've done some research and I found that both database have just minor structure differences between tables and no IDENTITY columns.
So, aside from the minor structural differences, I'd say it'd be mostly possible to just INSERT values from the old database back to the first one.
The database is actually somewhat big and has more than 100 tables.
What I want to know is, would it make sense to use SSIS to do this, or is SSIS suited to this kind of job? Or should I just write T-SQL code to do the job?
As an additional question, are there any tools more suited than the first two options above to this kind of job?