Database SQL – Bulk Insert And Update

---

Introduction

---

This tutorial helps to explain and write complex SQL Query which needs to create temporary tables and does bulk update or insertion.
This is a common requirement for every project where data migration required to fetch the data from various tables and insert or update the records in other tables. The user can write a Stored Procedure to achieve this functionality although sometimes we don’t have time to write and execute the Stored Procedure and want to complete the task through the SQL query.
Please find some SQL queries which helps to fetch the data from a various table and create a temporary table on the fly.

 

---

The WITH clause

---

The SQL WITH clause is good when used with complex SQL statements rather than simple ones. It also allows you to break down complex SQL queries into smaller ones which make it easy for debugging and processing the complex queries. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

• The WITH clause is used to fetch the records from different table and construct a temporary table.
• Queries that have an associated WITH clause can also be written using nested sub-queries but doing so add more complexity to read/debug the SQL query.
• The name assigned to the sub-query is treated as though it was an inline view or table

 

Syntax:

WITH temporaryTable (averageValue) as
    (SELECT avg(Attr1)
    FROM Table),
    SELECT Attr1
    FROM Table
    WHERE Table.Attr1 > temporaryTable.averageValue;

 

Example1:

WITH temporaryTableName as
    (SELECT table1.attr1, table1.attr2, table2.attr3,table2.attr4
    FROM table1, table2)
    SELECT *
    FROM temporaryTableName 
    WHERE attr1 > 100;

 

Example1:

WITH temporaryTableName1 as
    (SELECT table1.attr1, table1.attr2, table2.attr3,table2.attr4
    FROM table1, table2),
temporaryTableName2 as (
SELECT table3.attr1, table3.attr2, table4.attr3,table4.attr4
    FROM table3, table4
)
    SELECT *
    FROM temporaryTableName1 tn1
    JOIN temporaryTableName2 tn2 ON tn1.attr1 = tn2.attr2
    WHERE tn1.attr1 > 100;

 

---

The Bulk Insertion

---

The Bulk Insertion is very common and required by all most all the project. It is quite easy to write SQL Query and store the data into the temporary table and insert the records into the actual table. Sometime, The user would not have access to create a temporary table to store the intermediate data.
Don’t worry, we have another approach to solving this problem.
With the help of WITH clause, We can develop the complex SQL Query to do a bulk insertion. Please find an example below –

 

INSERT INTO FINAL_TABLE (COL1, COL2, COL3)

SELECT ATTR1, ATTR2, ATTR3 FROM (

WITH TEMP1 AS (

SELECT DISTINCT TABLE1.ATTR1, TABLE1.ATTR2, TABLE2.ATTR3, TABLE2.ATTR4, TABLE3..ATTR5 FROM TABLE1

JOIN TABLE1 ON TABLE1.ATTR1 = TABLE2.ATTR1

JOIN TABLE2 ON TABLE1.ATTR3 = TABLE2.ATTR3

JOIN TABLE3 ON TABLE3.ATTR1 = TABLE3.ATTR1

) SELECT ATTR1, ATTR2, ATTR3 FROM TEMP1 

);

 

---

The Bulk Update

---

The Bulk Update is also very common and required by all most all the project. We will write the SQL query to update the bulk records with runtime values. Please find the sample example below –

 

WITH TEMP1 AS (

SELECT DISTINCT TABLE1.ATTR1, TABLE1.ATTR2, TABLE2.ATTR3, TABLE2.ATTR4, TABLE3.ATTR5 FROM TABLE1

JOIN TABLE1 ON TABLE1.ATTR1 = TABLE2.ATTR1

JOIN TABLE2 ON TABLE1.ATTR3 = TABLE2.ATTR3

JOIN TABLE3 ON TABLE3.ATTR1 = TABLE3.ATTR1

)

UPDATE TABLE5 SET TABLE5.ATTR5= (SELECT ATTR5 FROM TEMP1 WHERE TABLE5.ATTR1 = TEMP1,ATTR1) 

WHERE TABLE5.ATTR6 >100;

 

The above SQL Query helps to Insert and Update the data, Please write the SQL using the original table and try.

---