I have a database with two tables. Each table has different structure but they do share few common fields.
Table1 field structure:
Field1, Field2, Field3, Field4
Table2 field structure:
Field1, Field4, Field3, Field5, Field6
Of course my database has better named fields and much more of them. As you can see both tables have Field1 (master key), Field3 and Field4.
Now I'm wondering if there is any possibility to somehow merge the data from these two tables into one, preferably without actually copying and duplicating the data (use of database relations or queries).
The third table should contain all common fields and some others (blank cells when there is no data available or perhaps some default data).
I know that in the long run it would be best to modify the database structure so that all common fields are stored in one table and all other fields in other relational tables but at this time I'm not allowed to make any changes to existing tables as my boss is afraid that this could lead to existing systems to crash.
There is a plan to update and finally merge those systems into one (that includes database restructuring) but I do need to do some statistical analysis before then so I'm looking for quickly implementable solution.
Performance isn't of critical importance for this as this would be only a temporary solution but is still desired because I don't want to wait for too long for retrieving the data nor do I want to cause any significant slowdowns to the whole database system.
So any suggestions are welcome.