Is it possible to use the SELECT INTO clause with UNION

sql

prompt-engineering

best-practices

performance

byAlex Kataev·Oct 9, 2024

When combining multiple queries using UNION [ALL], you can definitely output the result into a new table employing SELECT INTO. The crucial foundation here is to guarantee uniform column structures across all participating queries. The example below demonstrates this:

SELECT ColA, ColB INTO NewTable
FROM (
    SELECT ColA, ColB FROM Table1
    UNION ALL
    SELECT ColA, ColB FROM Table2
) AS MergedQuery;

Always match column data types to escape any potential errors. This command rapidly forms NewTable that consolidates data from Table1 and Table2.

Stepping Up the UNION [ALL] Game with SELECT INTO

As you refine your prowess with SELECT INTO in tandem with UNION ALL, it's crucial to thoroughly understand the following facets:

You can limit the total number of records acquired from each table by implementing the TOP(n) clause. This is particularly useful when juggling large datasets:

-- Hand-picking top 100 singers from two tables to form a super band
SELECT TOP(100) * INTO SuperBand
FROM (
    SELECT * FROM PopSingers
    UNION ALL
    SELECT * FROM RockSingers
) AS DualGenre;

Giving aliases to your subqueries can significantly boost the clarity of your complex queries, making it easier for others (or future self) to decipher.
Always test your UNION ALL queries on a smaller subsample prior to deploying it on a large scale. This can save you from lengthy debugging sessions.
Upon utilizing SELECT INTO, don't forget to create necessary indexes on the newly created table aiding in faster query executions.
Be mindful that UNION omits any duplicates, but UNION ALL includes all occurrences. Accordingly, design your queries considering the duplication probability.

Maintaining All-round Accuracy in UNION operations

In order to achieve flawless executions of your SELECT INTO with UNION ALL, here are some best practices:

Matching column order and data structure

-- An apple-to-apple match ensures no fruit salad scenario
SELECT Col1 INT, Col2 VARCHAR(100) INTO OrderlyTable
FROM (
    SELECT Col1, Col2 FROM TableA
    UNION ALL
    SELECT Col1, Col2 FROM TableB
) AS OrderedUnion;

Ensure every SELECT statement upholds the same order of columns and the data types across each column are compatible.

Gauging performance trade-offs

UNION vs UNION ALL: Taxed with removing duplicates, UNION could be slower than UNION ALL. If no qualms about duplicates, opt for UNION ALL for a performance boost.
Temporary tables: Stashing intermediate outputs in temporary tables can be handy if your results become substantially large.
INSERT INTO vs SELECT INTO: If your destination table exists already, INSERT INTO can be utilized instead of SELECT INTO:
```
-- The more, the merrier
INSERT INTO ExistingTable (Col1, Col2)
SELECT Col1, Col2 FROM OtherTable;
```