Creating one Table from three different Tables

问题:

I have three tables in SQL and I need them to all be combined into one. I need all the fields from all the tables in the one table. All the tables contain the same fields just from three different years. I wrote a code that is:

CREATE TABLE COL_TBL_TRAINING_ALL_YEARS AS(
SELECT 
COL_TBL_2010_TRN_RESULTS_new.*,
COL_TBL_TRN_RESULTS_GEMS_2011.*, 
COL_TBL_TRN_RESULTS_GEMS_2012.*
FROM COL_TBL_2010_TRN_RESULTS_new,
COL_TBL_TRN_RESULTS_GEMS_2011, 
COL_TBL_TRN_RESULTS_GEMS_2012
WHERE COL_TBL_2010_TRN_RESULTS_new.SYS_EMP_ID_NR = COL_TBL_TRN_RESULTS_GEMS_2011.SYS_EMP_ID_NR = COL_TBL_TRN_RESULTS_GEMS_2012.SYS_EMP_ID_NR)

And I get an incorrect syntax near the word 'AS' error and incorrect syntax near '='

I have read my SQL books and cannot seem to find an explanation on the method to do this, any help would be greatly appreciated.

回答1:

You need two do two step:

1. Use the UNION ALL, like it was mentioned. But if a column is missing you should use NULL for that column.

2. You should insert the result in a new table.

So it should look like:

SELECT * 
INTO   yournewtablename 
FROM   (SELECT col1, 
               col2, 
               col3 
        FROM   col_tbl_2010_trn_results_new 
        UNION ALL 
        SELECT col1, 
               col2, 
               col3 
        FROM   col_tbl_trn_results_gems_2011 
        UNION ALL 
        SELECT col1, 
               col2, 
               NULL AS Col3 
        FROM   col_tbl_trn_results_gems_2012) n 

Here is a demo to show: SQL FIDDLE

回答2:

You need UNION ALL:

SELECT * FROM COL_TBL_2010_TRN_RESULTS_new UNION ALL
SELECT * FROM COL_TBL_TRN_RESULTS_GEMS_2011 UNION ALL 
SELECT * FROM COL_TBL_TRN_RESULTS_GEMS_2012

回答3:

Your syntax appears essentially correct, assuming that your subquery returns a result. Two things you might want to try:

1. Run the SELECT statement by itself to ensure that it is correct.
2. Put a space between the AS and the parenthesis.

According to this page, the parentheses are required.