How to Insert Multiple Rows in SQL

calendar Jul 8, 2020 · 5 min read  ·
Share on: linkedin copy

Do you need to insert multiple rows into an SQL database?

You could do that separately using INSERT statements, but there are a few problems with this.

Learn what these problems are and how to insert multiple rows in SQL in this article.

Problem

You've got a bunch of data to insert into a table. This could be a small number of rows (say, 3 or 4) or there could be tens or hundreds of rows.

One way to get the data in is to write separate INSERT statements.

For example:

1INSERT INTO customer (first_name, last_name) VALUES ('Kristen', 'Rowley');
2INSERT INTO customer (first_name, last_name) VALUES ('Jed', 'Tomlinson');
3INSERT INTO customer (first_name, last_name) VALUES ('Margie', 'Escobar');
4INSERT INTO customer (first_name, last_name) VALUES ('Harriette', 'Mejia');
5INSERT INTO customer (first_name, last_name) VALUES ('Francis', 'Little');

This will get the data into the table.

However, there are a couple of problems with this method.

It's slow

Running separate INSERT statements can be quite slow. Each statement has to be processed and executed separately. Data is written to the table separately.

All of this overhead can make it slow when you want to insert a lot of records.

It's a lot of code

When you write separate INSERT statements like this, there is a lot of repeated code. The INSERT INTO, table name, and columns are all repeated.

Ideally, we would not have to repeat this.

Errors can cause half-entered data

If you get an error halfway through your series of INSERT statements, the row with the error is not inserted, but all rows before it are inserted.

You'll need to fix the error and rerun the statements, but only run the statements that did not insert.

Running only part of a statement could be a good thing, as you get some of the result, but it can take time to find the point of failure and run the right commands.

Let's take a look at how we can insert multiple rows in SQL.

 

SQL Insert Multiple Rows

Here's an example of batch inserting rows in SQL. It will work for MySQL, SQL Server, and PostgreSQL. It won't work for Oracle as they don't support this format, but there's another way you can do this in Oracle which I've detailed later in this article.

Let's create a simple customer table with two fields:

1CREATE TABLE customer (
2  first_name VARCHAR(100),
3  last_name VARCHAR(100)
4);

Now, we can INSERT multiple rows in SQL by repeating the list of values inside the brackets:

1INSERT INTO customer (first_name, last_name) VALUES
2('Kristen', 'Rowley'),
3('Jed', 'Tomlinson'),
4('Margie', 'Escobar'),
5('Harriette', 'Mejia'),
6('Francis', 'Little');

We have a single INSERT INTO command, and specify the columns to insert into once. We then specify the keyword VALUES. Finally, we add each of the rows we want to insert inside brackets, separated by a comma.

This should insert 5 rows into the table.

There are a couple of things to keep in mind about this method.

  • You can insert up to 1,000 records as part of an INSERT statement using this method. If you need to insert more, consider using multiple INSERT statements.
  • Also, on MySQL, the maximum size of the entire INSERT statement must be smaller than the database parameter "max_allowed_packet", which is the number of bytes. This can be changed (I'll write about this in another article).

 

SQL Insert Multiple Rows for Oracle

The above method for inserting multiple rows in SQL won't work for Oracle. But here's a method that does work for Oracle. It's slightly different but it gets the job done.

1INSERT ALL
2INTO customer (first_name, last_name) VALUES ('Kristen', 'Rowley')
3INTO customer (first_name, last_name) VALUES ('Jed', 'Tomlinson')
4INTO customer (first_name, last_name) VALUES ('Margie', 'Escobar')
5INTO customer (first_name, last_name) VALUES ('Harriette', 'Mejia')
6INTO customer (first_name, last_name) VALUES ('Francis', 'Little')
7SELECT * FROM dual;

This syntax is Oracle-specific as the other version won't work in Oracle.

There are a few things to notice here:

  • The INSERT ALL keyword is used to instruct the database to insert all records.
  • The INTO, table name, column names, and VALUES keyword are repeated for every row. This is quite a bit of code repetition but needs to be done.
  • There is no comma after each of the INTO lines. I often forget this and get syntax errors. So, don't add a comma.
  • We need to add SELECT * FROM dual at the end. The INSERT ALL statement requires a SELECT statement, and because we're just inserting values we specify, we don't need any data from a table. So we select from the "dual" table to get the statement to work.
  • If you add use a sequence and call the nextval value, then the same row will be used for each inserted row.

 

SQL Insert from Another Table

If you have your data in another table and want to insert it into a new table, you can use an INSERT statement and a SELECT statement. This works in all database vendors.

1INSERT INTO customer (first_name, last_name)
2SELECT fname, lname
3FROM list_of_customers
4WHERE active = 1;

This example will insert data into the customers table from data in the list_of_customers table where the value of active is 1.

There is no limit to the number of rows that can be inserted here.

 

Conclusion

So that's how you can insert multiple rows in SQL. Most databases follow the same syntax, except Oracle who requires it in a different format.

comments powered by Disqus