Appending Number of Tables

[Jonny]

I have two tables of 100.000 rows. Is any way to join them into one table?

 

[KeithG]

Yes, what do you mean by join? Relate the records or combine both tables to produce on result set? I assume you mean you want to display all records in one query so you will need to use an UNION query. See below

SQL statement

Union

SQL statement

 

[Jonny]

I don't want to display all the record by query. I try to create one database table that contains both tables.
for example:
table1:
123
456

table2:
234
567

New Table:
123
456
234
456

 

[KeithG]

That is exactly what the UNION operator will do.

 

[The_Doc_Man]

The other alternative - if you want the change to be permanent and no longer part of a query - is an APPEND query from the table that will go away to the table that will stick around. Assuming their field descriptions are the same, this should work perfectly.

IF THE TABLES ARE NOT DEFINED EXACTLY THE SAME, DON'T DO IT. Post that fact and get more specific advice.

 

[Jonny]

I've added following SQL statement:
Select * From a
UNION ALL Select * From b;
How I add to this statement third table (table c)?

 

[The_Doc_Man]

To add a third table to the UNION statement, assuming the third table to also have the same exact structure, add

UNION SELECT * FROM C ;

But remember, UNION only works when the elements named or implied from ALL participating SELECT clauses result in exactly the same list of fields of exactly the same data types presented in exactly the same order of fields.

There are ways to overcome that limitation, but not with the syntax you are using. "SELECT *" is pretty much a statement that you are asserting that you KNOW you have full field-level compatibility. If you are wrong, this syntax will fail you somewhere along the line.

 

[Jonny]

When I write:

Select * From a,
UNION ALL Select * From b;

It works nice, but when I add

Select * From a
UNION ALL Select * From b
UNION ALL Select * From c;

I'm getting "Syntax Error In FROM Clause"

Sorry for my question, all this really new for me.