Enhancing the performance of insert query from multiple tables using UNION

Question

I want to insert multiples tables into 1 tables at once.

Before this, I tried to union all the tables and create a new tables from it, but it takes hours.

select * from tables_1_1
union
select * from tables_1_2
union
select * from tables_1_3
union
select * from tables_2_1
...
until
tables_35_3

I thought, insert maybe the solution, or is there any better method to do this?

Answer 1

@OP, as I mentioned in the comments, consider to check this thread: Fastest options for merging two tables in SQL Server

Some points to highlight:

• Simple or Bulk-logged recovery and SELECT INTO .. FROM .. UNION ALL results into bulk inserts with no contention
• If SQL Server 2014+, consider to set compatibility level 110 or higher, this will enable paralelism during data load
• If SQL Server 2017 and the final table going to be extra large, consider to create a separate filegroup on the fastest storage and create table on this filegroup: SELECT * INTO Temp ON [NewFilegroup] FROM Table1. The number of files in the new filegroup preferably is equal to the number of cores
• Do not use UNION, use UNION ALL, otherwise SQL Server has to deduplicate data of all those 105 tables

• Data and log files on different storage so they do not interfere with each other

• On own risk: If speed of the load overweight possible risk, consider to ALTER DATABASE dbname SET DELAYED_DURABILITY = ALLOWED; and start load in transaction with DELAYED_DURABILITY = ON

General disclaimer: this post is based pretty much on a personal opinion

---

References:

• https://www.sqlservergeeks.com/select-into-on-filegroup/
• https://www.sqlshack.com/use-parallel-insert-sql-server-2016-improve-query-performance/
• https://dba.stackexchange.com/questions/12818/why-select-into-targettable-from-sourcetable-is-faster-than-insert-into-ta

Answer 2

I think you should work take into considerations two factors:

1. Hardware and server specifications
2. Data size

If the machine specifications you are working with are not very performant and you have a huge size of data, then distributing the INSERT operation (multiple INSERT) will enhance the performance because it will not consume the memory like a UNION operation. If the data size is acceptable and can be handled by SQL Server allocated memory, then you should use common table expression with a SELECT INTO query:

WITH CTE_1 as (SELECT * FROM TABLE_1 
               UNION ALL
               SELECT * FROM TABLE_2
               UNION ALL
               SELECT * FROM TABLE_3)
 SELECT * 
 INTo New_Table
 FROM CTE_1

Also note the difference between UNION and UNION ALL operations:

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results. Also try to avoid INSERT INTO and use SELECT INTO instead because it is minimally logged assuming proper trace flags are set.

Another thing to mention, (i didn't tested this approach but maybe it should gives better performance - and it may caused a huge index size) you should also try to create an indexed view over all tables (UNION query you have mentioned), then execute the query, as example:

SELECT * INTO ...  FROM vw_Unified

Update 1

If you are familiar with SSIS, performing data import process may gives better performance when using SSIS:

• Data Import Performance Comparison T-SQL vs SSIS for large import
• SSIS for table-to-table inserts vs. (SQL only) INSERT INTO () SELECT FROM approach
• Implementing Foreach Looping Logic in SSIS