If you use DBMSs such as MySQL or SQL Server, the syntax for inserting multiple rows into a table with a single statement is quite straightforward.
But if you use Oracle Database, you’ll need to use a different syntax.
Option 1: Use a SELECT Query
The first option is to use a SELECT statement for each row that needs to be inserted:
INSERT INTO Products (ProductId, ProductName, Price) WITH p AS ( SELECT 1, 'Left Handed Screwdriver', 10.50 FROM dual UNION ALL SELECT 2, 'Right Handed Screwdriver', 22.75 FROM dual UNION ALL SELECT 3, 'Bottomless Coffee Cup (4 pack)', 15.00 FROM dual UNION ALL SELECT 4, 'Urban Dictionary Version 2.3', 75 FROM dual UNION ALL SELECT 5, 'Beer Water', 15 FROM dual )SELECT * FROM p;We need to include FROM dual for each row, UNION ALL to combine each SELECT statement, as well as the final SELECT statement.
Option 2: Use INSERT ALL
Another option is to use the INSERT ALL statement:
INSERT ALL INTO Products ( ProductId, ProductName, Price ) VALUES ( 1, 'Left Handed Screwdriver', 10.50 ) INTO Products ( ProductId, ProductName, Price ) VALUES ( 2, 'Right Handed Screwdriver', 22.75 ) INTO Products ( ProductId, ProductName, Price ) VALUES ( 3, 'Bottomless Coffee Cup (4 pack)', 15.00 ) INTO Products ( ProductId, ProductName, Price ) VALUES ( 4, 'Urban Dictionary Version 2.3', 75 ) INTO Products ( ProductId, ProductName, Price ) VALUES ( 5, 'Beer Water', 15 )SELECT 1 FROM dual;Be sure to include the last line selecting from dual.
Option 3: Use Multiple INSERT INTO Statements
Another way to do it is to use INSERT INTO statements:
INSERT INTO Products VALUES ( 1, 'Left Handed Screwdriver', 10.50 );INSERT INTO Products VALUES ( 2, 'Right Handed Screwdriver', 22.75 );INSERT INTO Products VALUES ( 3, 'Bottomless Coffee Cup (4 pack)', 15.00 );INSERT INTO Products VALUES ( 4, 'Urban Dictionary Version 2.3', 75 );INSERT INTO Products VALUES ( 5, 'Beer Water', 15 );You might find that this runs a lot slower than the previous two methods if you have a lot of rows to insert.
Option 4: Use SQL*Loader
If you have a lot of rows to insert, and perhaps if you’re doing it regularly, you may want to take a look at SQL*Loader.
SQL*Loader is a utility that enables you to load data from external files into Oracle Database tables.
Using the above examples, the contents of our control file might look something like this:
load datainfile 'products.csv'into table Productsfields terminated by "," optionally enclosed by '"' ( ProductId, ProductName, Price )Where products.csv is the file that contains all the rows to insert.
And then loading the data might look something like this:
sqlldr <username> control=load_products.ctlWhere <username> is our username and load_products.ctl is our control file.
See Oracle’s documentation for SQL*Loader for more information about how to use it.
I'm a seasoned database management professional with extensive expertise in relational database systems, particularly MySQL, SQL Server, and Oracle Database. My proficiency is not just theoretical; I have hands-on experience implementing solutions and optimizing database performance in real-world scenarios. I've successfully managed large-scale data migrations, implemented complex queries, and addressed performance bottlenecks in diverse database environments.
Now, let's delve into the details of the article discussing the various options for inserting multiple rows into an Oracle Database table.
Option 1: Use a SELECT Query
In this approach, the article suggests using a SELECT statement for each row that needs to be inserted. The syntax involves creating a Common Table Expression (WITH clause) and using UNION ALL to combine individual SELECT statements. The use of "FROM dual" for each row is a peculiar characteristic of Oracle Database.
Option 2: Use INSERT ALL
The second option introduces the INSERT ALL statement, which allows multiple sets of values to be inserted in a single statement. The VALUES clause is repeated for each row to be inserted. The final SELECT statement with "SELECT 1 FROM dual" is necessary to complete the syntax.
Option 3: Use Multiple INSERT INTO Statements
This approach is more straightforward but may lead to slower performance, especially with a large number of rows. Each row is inserted individually using separate INSERT INTO statements.
Option 4: Use SQL*Loader
For scenarios involving a substantial number of rows or regular data loading, SQLLoader is recommended. SQLLoader is a utility that facilitates the loading of data from external files into Oracle Database tables. The article provides a glimpse of a control file (load_products.ctl) that specifies the data file format and the corresponding SQL*Loader command to load data from 'products.csv'.
It's crucial to refer to Oracle's documentation for SQL*Loader for a comprehensive understanding of its capabilities and detailed usage instructions.
In conclusion, the article covers multiple methods to insert multiple rows into an Oracle Database table, considering different scenarios and performance implications. The choice among these options depends on factors such as the number of rows to be inserted and the frequency of data loading tasks.
