Creating one Table from three different Tables

Question

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.

possible duplicate of [How to create table using select query in SQL Server?](http://stackoverflow.com/questions/11114441/how-to-create-table-using-select-query-in-sql-server) or [Try to create a table from Select - SqL Server 2008 throws error](http://stackoverflow.com/questions/11940477/try-to-create-a-table-from-select-sql-server-2008-throws-error) — LittleBobbyTables - Au Revoir, Mar 04 '13 at 14:44
Possible duplicate of [How to create table using select query in SQL Server?](https://stackoverflow.com/questions/11114441/how-to-create-table-using-select-query-in-sql-server) — Brian Tompsett - 汤莱恩, Mar 24 '18 at 10:57

score 10 · Accepted Answer · edited Dec 18 '13 at 15:20

10

You need two do two step:

Use the UNION ALL, like it was mentioned. But if a column is missing you should use NULL for that column.
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

edited Dec 18 '13 at 15:20

Tushar Gupta - curioustushar

58,085
24
103
107

answered Mar 04 '13 at 15:12

András Ottó

7,605
1
28
38

I added in all the columns and the nulls i needed and I am getting the syntax error near ')' I removed the "n" too because I figured that was a typo – user2119980 Mar 04 '13 at 15:41
The N is needed there it is the name of the inner table. That is the reason of your syntax error. Try the SQL FIDDLE demo, if you remove the "n" you get the same error. – András Ottó Mar 04 '13 at 16:16
The tables contain over 20,000,000 rows all together. I deleted one, combined the other 2, deleted one of those, then readded the first table I deleted and now I am trying to combine the data from the 2 tables. I cannot create a new table due to the fact that there is not enough room to house the data so I need to just combine the 2 tables to form 1 table so in the end it will contain the data from all 3 tables. Any Ideas? – user2119980 Mar 04 '13 at 20:54
Maybe you can try with a VIEW, if you have as many record and no additional space. – András Ottó Mar 05 '13 at 05:53
If I need to have a table (lets say school) - that offers many courses. And these courses are rows in the table of "schoolcourses". How can my school table contain a a list of its school courses? – Lealo Aug 16 '17 at 02:21
You have to identify the relation between the two tables: It is a 1 to n or an m to n relation. Let say course "Basic SQL" is only for one school or is there multiple school offering this course. In case of 1 to n the schoolcourses needs to have a school id as a foreing_key in case of n to m, you need a table which orders a course id to a school id. I think you should ask this as a separate question, because this is a different topic. – András Ottó Aug 17 '17 at 12:54

score 1 · Answer 2 · answered Mar 04 '13 at 14:41

1

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

answered Mar 04 '13 at 14:41

I'm pretty sure that "CREATE TABLE AS SELECT" isn't valid SQL Server syntax at all, unless they changed something in 2012 that I missed. – LittleBobbyTables - Au Revoir Mar 04 '13 at 14:43
I am getting the error All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. I forgot to mention on of the tables is missing one column the other two tables have – user2119980 Mar 04 '13 at 14:45
@LittleBobbyTables: My query doesn't include `CREATE TABLE AS SELECT`. – Mar 04 '13 at 14:51
@user2119980: Actually, your question explicitly states: **"All the tables contain the same fields just from three different years."** Add the table structures to your question and I'll amend the query accordingly; alternatively, add the missing field to the relevant table and the query should work as it is. – Mar 04 '13 at 14:55

score 1 · Answer 3 · answered Mar 04 '13 at 14:49

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

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

According to this page, the parentheses are required.

Creating one Table from three different Tables

3 Answers3

Linked