Create a single table from multiple tables with the same structure in SQL

If you're looking for a way to accomplish in SQL what an append query was to Access, you're in luck. The trick is the UNION keyword. This will combine two or more tables together into one. For example, say I have two database tables. Each table contains a list of people and includes their SSN, first name, and last name. The names of these tables are people1 and people2. Rather than having to look at each of those table individually, you can simply combine them with the UNION

SELECT * FROM people1 UNION SELECT * FROM people2

That will give you a single result set that contains all distinct records from both people1 and people2. If you had more tables you could just add more UNIONs

SELECT * FROM people1 UNION SELECT * FROM people2 UNION SELECT * FROM people3 UNION ...

One drawback with this is that UNION only returns distinct records. However, if you want to see everything from the tables you're joining whether it exists in more than one table or not, simply use UNION ALL instead. This returns all results, and does not require them to be distinct from one another.

SELECT * FROM people1 UNION ALL SELECT * FROM people2

This would return all rows from both people1 and people2, and if a person was listed in both tables they would show up twice in the result set.

If you're looking for a free SQL tutorial, or just more about using UNION, check out what W3Schools.com has to offer on their site. I've used that site for everything from SQL to HTML, and feel it has been one of the more helpful resources I've run across.