Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to generate data with Union ALL and Insert ALL in Oracle?
Problem:
You wanted to know the difference between Union ALL and Insert ALL to generate small amounts of data.
Solution:
I have been recently generating small amounts of data for testing some functionality and came across a couple of options in Oracle. Union All and Insert All are two options commonly used for generating small amounts of data in Oracle.
The most common set operators in Oracle are UNION and UNION ALL. These operators are used to combine sets of data, even if there are no relationships between these sets.
UNION creates a distinct set, whereas UNION ALL allows for duplicates. Removing duplicates can have a huge performance impact, so it is ideal to use UNION ALL by default and later on handle the duplicates.
The most common use of UNION ALL is to generate data. In Oracle, we must always select from something, so there is a special pseudo-table named DUAL.
The DUAL table only has one row, so if we want to create multiple rows, we must combine statements with UNION ALL like this
Example
SELECT 'something' FROM DUAL UNION ALL SELECT 'something_else' FROM DUAL UNION ALL SELECT 'something_more' FROM DUAL UNION ALL
I have used UNION ALL as shown below to generate the data and insert into my target table
Example
-- CREATE a table CREATE TABLE tennis_stats ( tennis_player VARCHAR2(100), grandslam_titles NUMBER(2) ); COMMIT;
Example
-- Generating data using Union All INSERT INTO tennis_stats SELECT REGEXP_SUBSTR(t.tennis_stats, '[^,]+', 1, 1) tennis_player, REGEXP_SUBSTR(t.tennis_stats, '[^,]+', 1, 2) grandslam_titles FROM ( SELECT 'ROGER FEDERER' || ',' || 20 AS tennis_stats FROM DUAL UNION ALL SELECT 'RAFAEL NADAL' || ',' || 19 AS tennis_stats FROM DUAL UNION ALL SELECT 'NOVAK DJOKOVIC' || ',' || 17 AS tennis_stats FROM DUAL ) t ; COMMIT;
INSERT ALL
Oracle SQL has a multi-table insert operation that lets us insert data into multiple tables in one single statement. Multi-table inserts are useful when we have one source that populates multiple destinations.
The INSERT ALL syntax was intended to add data to multiple tables, but I commonly use to generate data and insert multiple rows into the same table.
For example, let’s say we want to load the same data shown above using Insert All Option.
Example
-- CREATE a table CREATE TABLE tennis_stats ( tennis_player VARCHAR2(100), grandslam_titles NUMBER(2) ); COMMIT;
Example
-- Generating data using Insert All INSERT ALL INTO tennis_stats VALUES ( 'ROGER FEDERER', 20) INTO tennis_stats VALUES ( 'RAFAEL NADAL', 19) INTO tennis_stats VALUES ( 'NOVAK DJOKOVIC', 17) SELECT * FROM dual; COMMIT;
Note INSERT ALL does not use sequences. INSERT ALL only increments the sequence once per the statement, not once per reference.
There’s a minor issue with the INSERT ALL statement – the above code doesn’t list all the column names. In a real-world SQL statement, we wouldn’t want to waste so much space repeating the same list of columns. Also, excluding the column names future-proofs the query.
If a future version of the table tennis_stats adds a new column, the preceding statement would raise an exception, instead of silently writing bad data. On the other hand, listing the column names ensures the values go in the right columns, even if the column order changes. Excluding column names is a debatable style choice.
Conclusion
The INSERT ALL looks neat and easy, but the UNION ALL is overall better for generating data. The INSERT ALL statement is slower to parse than UNION ALL, especially for large statements with hundreds of rows.
If our applications are generating lots of data, an INSERT ALL is better than multiple INSERT statements.
In conclusion, Insert ALL looks neat but really not meant for generating large amounts of data.
808 Views
