How to combine data from two tables into one table?

[sammx]

Hi All,
I have two tables with same structures/fields, but with different data. Can I combine all data from these two tables into one table?
For example, Table1 Table2 Table3
#records 500 500 ==> 1000

Thanks

 

[sko]

You can use an append query to combine the records but you should tell us more about the tables and how they are related if you want more advice. Primary keys? Foreign keys? Autonumber fields? The number of records is really irrelevant.

 

[sammx]

HI Sko,
Thanks for your reply.
These two tables have same fields: 13 fields.
Those fields are: Title, Author, ISBN, Edition, Pages, PubDate, Image..etc.
The Primary Key is ISBN, and there is no AutoNumber fields.
The data type of these fields are text, number, and Date.
Thanks!

 

[sko]

I would suggest a union query but I don't know how familiar you are with writing SQL, so we'll create an append query instead. We could append the records from Table1 to Table2 but it looks like you're wanting to keep those tables intact. At any rate, we'll append to new Table3 in case the results aren't what you expected.

Since ISBN is the primary key in all three tables, you will be prevented from adding duplicate records.

1. If you haven't already created Table3, you can copy and paste the design from Table1 and append the data from Table1 all in one step. From your list of tables, select Table1 and do a copy and paste. In the 'Paste Table As' dialog name the table (Table3) and choose the 'Structure and Data' paste option.

Now, to add Table2 data
2. Open a new query in design view. Add Table2. By default, this will be a select query. Place all the fields from Table2 in the query grid, one column for each field.
3. Change to an append query by selecting it from the Query drop-down menu. You will be prompted for a table name. Select Table3 in your current database.
4. Since the fields have the same name in both tables, the append row of your query will be filled in automatically.
You are saying 'append ISBN field from Table2 to the field named ISBN in Table3'. If the field in the new table doesn't happen to have the same name, select the appropriate field from the combobox in the append row.

Hope this helps

 

[LittleSmudge]

One question worth asking sammx before following sko's lead,

Is there any good reason why you might want to work with the two original separate table in the future?

Ie. Are you wanting to combine then now just to do a specific job and in future you'll work with them individually
OR
Is this a permanent change to the database schema ?

If it is the second then follow sko.

If it is the first then sko's original idea of a UNION query would be the better way to go.


Union queries are not diffecult and they don't disrupt the original data.


'ope-that-'elps.

G LS

 

[mikey69]

You could also run two queries, first a make table to make a new temporary table, then an append query. This has a number of advantages. It doesn't disturb the original data and it can be set to re-run whenever the underlying data changes significantly. This can even be done programmatically through VBA

 

[sko]

Good point LS. I was assuming a change to the database schema because I use append queries to convert old flat file databases.

 

[sammx]

Thanks for all those great responses!!!
Actually, the total records are about one million!
Therefore, I have to ask several people to insert data with same table but seperate tables.
And I did resolve this problem from your great response.
sammx