Have you ever needed to insert multiple rows into a table in SQL?
One way to do this is with separate INSERT statements.
But there is another way that results in less SQL code and can be faster.
Let’s see what this other way is and how we can do it.
First, here’s the situation. You’ve got a bunch of data that you want to insert into a table.
This could be a small number of rows, such as 3 rows, or there could be tens or hundreds of rows.
One way to do this is to write separate INSERT statements.
Here’s an example. We want to insert 5 rows into the customer table. To do this,
we have individual INSERT statements, each of which insert a different record into the table.
If we run these statements, the data will be inserted into the table.
However, there are a couple of problems with this method.
Firstly, it’s slow. Running separate Insert statements can be quite slow. Each statement
has to be processed and executed separately. This might not make a difference for only a
few records, but it can be quite a lot of overhead if you want to insert a large number of records.
Another problem is that there is a lot of repeated code. We’re repeating the Insert Into,
and the table name, and the columns. Ideally we wouldn’t want to repeat this.
Another issue is that if you get an error at any point while running your series of Insert
statements, the row that had the error is not inserted. But, all rows before it are inserted.
You’ll need to fix the error and rerun the Insert statements,
but only run the statements that did not inserted. Otherwise you’ll get duplicate values.
Running only part of your set of statements may be a good thing, as you get some of the results,
but it can take some time to find the issue and run the right commands.
You could use a transaction for this,
which has its own benefits, and I’ll cover that in another video.
Let’s look at another way we can insert multiple rows in SQL.
It’s a variation of the Insert statement, and it looks like this.
We specify the insert into, and the table name and column names.
After the Values clause, we specify the brackets and the series of values to insert.
However, after the brackets, we add a comma,
and then another set of brackets that includes the data for the second row.
We can repeat that section - the brackets, data, and comma - for every row we want to
insert. In this example, we have 5 rows to insert, because there are 5 sets of brackets and data.
If we run this statement on our database, 5 rows will be inserted. This syntax,
or format of Insert statement, will work on SQL Server, MySQL, and PostgreSQL. It won’t work on
Oracle, because Oracle doesn’t support this format of the Insert statement unfortunately. I wish it
did! I think it’s great! There is another format that Oracle can use which I’ll explain shortly.
There are a couple of things to keep in mind with this approach.
You can insert up to 1,000 records as part of an Insert statement using this
approach. If you need to insert more records, consider using multiple Insert statements.
If you are using this on MySQL, the maximum size of the Insert statement must be smaller than the
database parameter called “max_allowed_packet”, which is the number of bytes.
If you’re on Oracle, the format for inserting multiple rows is different,
but it gets the job done.
Here’s what it looks like. This format won’t work on other databases, it’s only for Oracle.
First, we have the word Insert All. This is a specific type of Insert statement that is designed
for inserting data into multiple different tables, but we’re using to insert into the same table.
We then have multiple rows of data, each of which specify Into Customer,
then the columns, and the values keyword. This is repeated for each row we want to insert.
The brackets and data are also mentioned here for each row, so we insert 5 different rows.
There are no commas at the end of each row, unlike the Insert statement we saw earlier in the video.
Finally, at the end, we have a Select statement. This is a required parameter of the Insert All
statement, because it’s designed to get data from a table and insert it into other tables.
But, we don’t want to get data from a table, we just want to insert multiple rows that we specify.
So, in order to do this, we specify a dummy table in Oracle called “dual”.
This will mean that the statement will run and not insert data from any other table.
One thing to keep in mind with this Insert All statement is that if you use a sequence,
which is an object that generates unique values,
and call the nextval value, you’ll get the same value each time.
Here’s an example query. We see the sequence and its nextval used for the ID column.
When we run this, the same value will be generated once and used for each row,
which is likely not what you want to do.
There are a few benefits of using this multiple insert approach.
It’s often faster than individual insert statements because it’s all processed at once
and the overhead of individual statements is not present.
This can be useful if you have a lot of data to insert.
Also, if one record fails, the whole statement fails,
and none of the records are inserted. This means you can find and fix the issue and
rerun the statement, making it easier to insert data and not get duplicate records.
It’s also less code. In the Oracle example,
there is still quite a lot of repetitive code, but the other benefits still apply.
So, in summary, we can use the Insert statement to insert multiple rows into a table.
There’s a simple variation used for SQL Server, MySQL, and PostgreSQL,
while Oracle uses another variation called Insert All.
And that brings us to the end of this video on inserting multiple rows in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the syntax for inserting multiple rows,
or how to do it in Oracle, or something else?
Thanks for watching.