Welcome to another tutorial on SQL INSERT queries for beginners, in this particular article I am going to explain how to perform SQL queries for multiple insertsthat is, make an INSERT query that avoids you making many of the same queries directly in a few lines of code.
To make this tutorial fast and efficient for you I am going to present several descriptive examples. You will see that, once you have seen a couple, there are no more varieties of this type of query.
- SQL Insert multiple rows at once
- MULTIPLE SQL INSERT example
- Example 2 INSERT MULTIPLE SQL
- Notes on the SQL INSERT MULTIPLE query
- Contribute your knowledge or doubts about the multiple INSERT
SQL Insert multiple rows at once
To insert multiple records at once with a single INSERT, we need to know the structure of an INSERT query. As a refresher, here are the two ways to perform a simple insert query:
INSERT INTO tabla VALUES ( valor1, 'valor_texto' ); o INSERT INTO (campo1, campo2, campo3) VALUES ( valor1, valor2, valor3);
Now, with this structure in mind, you just have to know that to insert several records in the same query, you just have to repeat the list of values to insert.
With repeat I mean its structure, since its values will be what you need to insert.
( valor1, valor2, valor3, valorN), (valor1, valor33, valor42, valorN), valor1, valor33, valor42, valorN)
These value groups between parentheses and separated by commas will make up each of the records to be inserted.
MULTIPLE SQL INSERT example
Now we can see what the complete INSERT MULTIPLE SQL query would look like:
INSERT INTO tabla (campo1, campo2, campoN) VALUES ('valor1registro1', 'valor2registro2'), ('valor1registro2', 'valor2registro2'),('valor1registro3', 'valor2registro3');
Example 2 INSERT MULTIPLE SQL
Just like a normal SQL INSERT query, we could also perform this query without specifying the fields/columns at the beginning of the insert:
INSERT INTO tabla VALUES ('valor1registro1', 'valor2registro2'), ('valor1registro2', 'valor2registro2'),('valor1registro3', 'valor2registro3');
Notes on the SQL INSERT MULTIPLE query
To finish I would like to leave some clarifications for all those who find it useful:
- The INSERT SQL query to insert multiple records at the same time has a 1000 record limit for each one. This means that we can make up to a thousand groups after the values.
- If we make a multiple insertion through an INSERT INTO SELECT query we will not have a record limit.
- As an observation, if you do an export of a SQL database with its values (can be in phpmyadmin or any other software), you can see that you will have the option to choose how to do these multiple inserts.
- As a reminder, even if it’s something simple, remember that the values and types of the different records must always follow the same order.
Contribute your knowledge or doubts about the multiple INSERT
I hope this tutorial has been of great help to you, if so, I would appreciate it if you left a comment.
It would also be of great help if you contribute something additional from your own experience or you can leave a question about the tutorial so that I can add more information.
Finally, if you found it useful, I would appreciate it if you shared the content to continue helping us create quality and easy tutorials for programmers around the world.