Bulk Data Insertion In SQL Table Using Union ALL

In this blog, you will learn about bulk data Insertion in SQL Table using Union ALL.

We are going to discuss bulk data insertion from multiple tables

• There are many scenarios in which we want to insert a large amount of data from one table to another which has the same schema and all things
• We discussed two ways to insert bulk data manually inside the SQL Table using UNION ALL
• Also, after selecting the data from one table using select query and then inserting that into another by using insert and select query with the help of union all

Let’s start

Step 1

Create a Product Table inside the database

CREATE TABLE DummyProduct (
    ProductID int,
    ProductName varchar(255),
    ProductDescription varchar(255),
    ProductPrice varchar(255),
    ProductStock varchar(255)
);

Step 2

First, we look manual process

select 1, 'Mobile', 'IPhone 12', 80000, 200 
UNION ALL
select 2, 'Laptop', 'HP Pavilion 15', 100000, 100
UNION ALL
select 3, 'TV', 'Samsung Smart TV', 35000, 300 

Here, you can see we use multiple select queries with union all, you will see the following output after executing all the above query

Step 3

Now, we are going to insert all data in the Product table in only one transaction

INSERT into Product
select 1, 'Mobile', 'IPhone 12', 80000, 200 
UNION ALL
select 2, 'Laptop', 'HP Pavilion 15', 100000, 100
UNION ALL
select 3, 'TV', 'Samsung Smart TV', 35000, 300 

Here see first we write insert query and below that put all select query using union all and when we execute all this bunch of SQL Query at a time then all the data which are present in the select query is inserted into the product table as shown below

(Note – The number of columns that are present in the select query will be the same as present in the targeted table)

Step 4

Now we are going to insert the Product table records into the new DummyProduct Table with adding new one record as shown below

--Create DummyProduct Table
CREATE TABLE DummyProduct (
    ProductID int,
    ProductName varchar(255),
    ProductDescription varchar(255),
    ProductPrice varchar(255),
    ProductStock varchar(255)
);

--Insert data in bulk
INSERT into DummyProduct
select * from Product
UNION ALL
select 4, 'Keyboard', 'HP Gaming Keyboard', 2000, 400 

//select all the record
select * from DummyProduct

Here, you can see we write insert query of DummyProduct and below that put two select query. One takes three records from Product table and the second one takes one record from the static select query, after executing all the above SQL Queries at a time you will see below output as I showed below.

This is all about the bulk insertion of the table using union all.

Happy Coding!