Merging two tables into one master table

[sgtpepper]

I have two tables that I want to merge into one. The problem is one table has 30 fields and the other has 40 fields.

For me to merge all the data does this mean I need to have my master table contain all 70 fields?

What is the proper way to setup the query?

I was thinking of writing a script that used a temp table to store the data from both tables and somehow insert into the master table.

Any thoughts on the best way of doing this?

[eoghain]

There is no "best" way of doing this. It's all dependant on your data and the specifics of your needs.

However, I will tell you that I think you really need to re-think what you are doing. First off having a single table with 30 fields seems like a very bad database design (It may be required in your situation, but I've never had the need for a table this large), and merging with another table to form a 70 field table seems even worse.

I like to design my tables as logical groupings of information, and I also like to only have required fields in my tables. This helps to optimize the storage of my data, and usually results in faster access to specific pieces.

I really, and strongly, recommend that you review exactly what your needs are for this data, and why you would want/need to have 70 fields of data stored in a single table. This would get to be un-wieldly when writing well formed queries (no 'select *'). Having multiple tables each with specific subsets of your data linked together with foreign keys is a much better solution (even if you are using fake foreign keys).