Geek Answers Handbook

Create one table from three different tables

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

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 the wrong syntax next to the error word "AS" and the wrong syntax next to "="

I read my books on SQL and it seems I can not find an explanation on the method for this, any help would be greatly appreciated.

+4
source share
3 answers

You need to do two things:

  • Use UNION ALL as mentioned. But if a column is missing, you must use NULL for that column.

  • You must insert the result in a new table.

So it should look like this:

 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: SQL FIDDLE

+4
source

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
+1
source

Your syntax looks essentially correct, assuming your subquery returns a result. Two things you can try:

  • Run the SELECT statement yourself to make sure it is correct.
  • Place a space between the AS and the brackets.

According to this page , brackets are required.

+1
source

More articles:


All Articles