Create a Master table from 3 other tables

[gmazza76]

Good afternoon,

I am reasonably new to Sql and I am trying to create a Query to update/create a new table "Overall Data" from 2 current Tables.

I am using the code below which works but I then need it to create a table if possible called "All Data" so I can then link it to PowerPivot
Currently in its Query state I cannot do this, so what is the best way to do this?

Select *
from Table1
Union all
select *
from Table2

thanks in advance
Gavin

 

[Micron]

I would think that you should be able to make an Access Make Table Query and use your UNION query as it's source? If not, use make table from the first part (SELECT...) then use an append query to append from the second SELECT.

 

[xenou]

In much the same way I would suggest you create the table yourself as recommended above, or by copying one of the original tables. Then you can use it freely:

delete * from NewTable;
insert into NewTable (
    Select *
    from Table1
    Union all
    select *
    from Table2
);
select * from NewTable;

I would note that the only draw back to use a make table query generally (whatever the circumstances) is that is creates no indexes or primary keys and is generally therefore not so great for tables with any decent amount of records in them - you really want those keys and indexes in real life situations where you have more than a few thousand records in your tables.

 

[gmazza76]

good afternoon,

thanks for the info @xenou, just a quick 2 questions if you can.
1. I haven't used the "primary key" as I wont need to interrogate the data after I pull it into the new table. Would this be an issue as I am pulling the new table into PowerPivot to do more work on it?
2. Slight issue with the code, do as I am getting an error "characters found after end of SQl statement @ INSERT

delete * from All_data;
insert into All_data(
    Select [field1],
    from Week_44
    Union all
    select [field1],
    from Week_45
);

thanks

 

[alansidman]

An alternative that is available to you as a work around is to bring each of the tables into Power Query and append each into a "Master." Once completed you can export or link back into Access.

 

[gmazza76]

thanks @alansidman, I haven really used PowerQuery, but each of the tables I want to combine hold around 500K of line data.
Will this slow everything down as when I have seen SQL do this before its fairly quick

 

[alansidman]

PQ is usually very quick and can handle that volume so long as the result is loaded to a Connection Only. Click on the link in my signature to learn more about PQ.

 

[gmazza76]

Thanks @alansidman.
do you or @xenou know how I would get round the issue of where my source file as the week goes on can add extra columns on.
As you can see from the code below Week_44 (as the week has completed) has 28 Fields and Week_45 (linked as a source file) only has 23 I seem to be having an issue as the "All_Data" is expecting a full 28.
Plus I am getting a syntax error at the "Union" point
Is there a way round this or will I have to adjust the SQL day by dady?

INSERT INTO All_data ( DateTag, SessionIDTag, DNISTag, CLITag, LOBusTag, SegTag, ICMTag, CountTag, PrompSTag, AppTag, CardTypeHintTag, AccountTypeHintTag, WildcardHintTag, ReturnCodeTag, CETag, Tag16, Tag17, Tag18, Tag19, Tag20, Tag21, Tag22, Tag23, tag24, tag25, tag26, tag27, tag28 )
SELECT Week_44.Field1, Week_44.Field2, Week_44.Field3, Week_44.Field4, Week_44.Field5, Week_44.Field6, Week_44.Field7, Week_44.Field8, Week_44.Field9, Week_44.Field10, Week_44.Field11, Week_44.Field12, Week_44.Field13, Week_44.Field14, Week_44.Field15, Week_44.Field16, Week_44.Field17, Week_44.Field18,
Week_44.Field19, Week_44.Field20, Week_44.Field21, Week_44.Field22, Week_44.Field23, Week_44.Field24, Week_44.Field25, Week_44.Field26, Week_44.Field27, Week_44.Field28
from Week_44
union all
SELECT Week_45.Field1, Week_45.Field2, Week_45.Field3, Week_45.Field4, Week_45.Field5, Week_45.Field6, Week_45.Field7, Week_45.Field8, Week_45.Field9, Week_45.Field10, Week_45.Field11, Week_45.Field12, Week_45.Field13, Week_45.Field14, Week_45.Field15, Week_45.Field16, Week_45.Field17, Week_45.Field18,
Week_45.Field19, Week_45.Field20, Week_45.Field21, Week_45.Field22, Week_45.Field23
FROM Week_45;

thanks
Gavin

 

[xenou]

Union requires the same number of columns from both sides of the join. If the data you are receiving sometimes has different numbers of columns that's another type of problem

 

[alansidman]

You may wish to review these

Merge Files With Different Column Headers

A client contacted me today asking how to merge files with different column headers in Power Query. The issue she's facing is that some of the files in her folder have a column called "customer", where others have a column … Continue reading →

www.excelguru.ca