Merge Two Tables

[krets]

I know this is probably a simple thing to do but I'm having one of those mornings where I can't figure it out and my Google mojo is failing me.

I have two tables of employees I need to merge together. All of the columns in the tables are exactly the same. I just need to merge the data in table A with the data in table B to create one table.

Please feel free to smack me and wake me up so I can get this done.

 

[dhookom]

Smack, smack, "union query", smack, smack "union query"

SELECT tblA.*, "A" as SourceTable
FROM tblA
UNION ALL
SELECT tblB.*, "B"
FROM tblB;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[krets]

So if I the table names are "PharmTechs" and "EmployeeTable" would I would use:

SELECT PharmTechs *, "PharmTechs" as SourceTable
FROM PharmTechs
UNION ALL
SELECT EmployeeTable *, "EmployeeTable"
FROM EmployeeTable;

???

I haven't had much experience with doing union queries. If I use that code, will it dump the new merged data into PharmTechs?

 

[krets]

Ah, got it.

After a little more research I used an insert query to insert the data from PharmTechs into EmployeeTable.

For future Googlers:

INSERT INTO EmployeeTable
SELECT List all fields
FROM PharmTechs;

 

[dhookom]

I wouldn't append records from one table into the other unless that was the expected/required result. If you only want to display, report, query them together without actually appending, your SQL would be:
SELECT PharmTechs.*, "PharmTechs" as SourceTable
FROM PharmTechs
UNION ALL
SELECT EmployeeTable.*, "EmployeeTable"
FROM EmployeeTable;



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[krets]

I wanted to append the records into one table. We are merging the two tables together permanently so we can track all employees in one database instead of two separate databases.