<< SQL Server 2021 Hosting - HostForLIFE :: How To Rename Database Objects In SQL Server | SQL Server 2021 Hosting - HostForLIFE :: XML Shredding In T-SQL >>

SQL Server 2021 Hosting - HostForLIFE :: Bulk Data Insertion In SQL Table Using Union ALL

July 19, 2022 09:31 by author

Peter

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.