How to Append Two Tables and Put the Result in a Table in SQL?

SQL provides the UNION and UNION ALL operators to combine data from two tables into a new table. These operators are used to merge rows from multiple tables into a single result set. The UNION operator removes duplicate rows from the combined result, while UNION ALL includes all rows, including duplicates.

In this article, we will explain how to use these operators with practical examples and show the output for each method. We will also provide tips and best practices for using UNION and UNION ALL effectively in our SQL queries.

Using UNION to Combine Tables

The UNION operator combines the results of two SELECT queries into a single result set, excluding duplicates. It merges the data from two tables into one, ensuring that each row in the final table is unique. The syntax for using UNION is as follows:

Syntax:

SELECT column_one, column_two,..column_N INTO Table_name FROM table_name
UNION SELECT column_one,column_two,column_three,.. column_N FROM table_name;

Suppose we have two departmental stores with data stored in two tables. Shop1 contains products like USB drives, pencils, and water bottles, while Shop2 stocks items such as nail files, rubber bands, and shoes. Each table provides a unique inventory snapshot.

Shop1 Table:

Shop2 Table:

Query Using UNION

To combine the data from Shop1 and Shop2 into a new table joined, excluding duplicates. This command merges the data from both tables, showing only unique records in the result set. Run the following command to see the output:

SELECT * INTO joined FROM Shop1
UNION 
SELECT * FROM Shop2;

Output

Using UNION ALL to Combine Tables

The UNION ALL operator also combines the results of two SELECT queries into a single result set but retains all rows, including duplicates. The difference between Union and Union All is UNION doesn't include duplicates, but UNION ALL includes duplicates too. Both are used with similar syntax. So, the syntax for UNION ALL is:

Syntax:

SELECT column_one, column_two,column_three,.. column_N INTO Table_name FROM
table_name UNION SELECT column_one, column_two, column_three,..column_N
FROM table_name;

Query Using UNION ALL:

To create a new table joined2 that includes all rows from Shop1 and Shop2 including duplicates, we use the following SQL command. This command combines the data from both tables and retains all entries, even if they appear in both Shop1 and Shop2.

Query:

SELECT * INTO joined2 FROM Shop1
UNION ALL 
SELECT * FROM Shop2;

Output

Conclusion

Using UNION and UNION ALL effectively allows us to merge data from multiple tables into a single table. While UNION filters out duplicates, UNION ALL retains all rows. Understanding the difference between these operators helps manage data accurately and efficiently in SQL databases. By using these methods, we can maintain data integrity, avoid errors, and manage complex queries effectively. By mastering the use of UNION and UNION ALL,we can easily handle complex data integration tasks, ensuring our SQL queries produce accurate and meaningful results.