SQL Insert Multiple Rows | Insert Multiple Rows In SQL
We can insert multiple rows in the SQL database using insert statement, insert into select statement, and Union All statement. Inserting multiple values in the rows is a tedious task when a table comes with a lot of attributes. In Laravel, we can add the whole array as rows and insert them in the database. We have used the DB query builder and eloquent model.
Previously, we were habituated to add values in row one at a time which is very time-taking and which also results in a lot of error. If we want to get rid of this, we are going to learn how to insert the multiple rows in a table at a time.
SQL Insert Multiple Rows
There are different methods to insert values in a table:
INSERT INTO table_name(column1, column2, ……., column n) VALUES (values in row 1), (values in row 2), ……… (values in row n);
1) Table_name is the name of the table.
2) Columns are the name of the columns.
3) Values in rows are the set of values to be inserted in the table.
1) At a time, you can insert 1000 rows using INSERT statements. To insert more rows than that used multiple insert keywords or use BULK INSERT.
2) Only SQL SERVER 2008 allows adding multiple rows in the table.
Suppose a table is created named as STUDENT.
INSERT INTO STUDENT (ID, NAME, CITY) VALUES (101,’Shubh’,’Kolkata’), (102,’Sandeep’,’Asansol’), (103,’Shouvik’,’Patna’);
See the output.
Using INSERT INTO SELECT statement.
INSERT into table_1 (column1, column2, …., column n) SELECT column1, column2, …., column n from table_2 ;
- table_1 is the name of the newly created table
- table_2 is the name of the previously created table.
- Columns are the respective columns in which you want to insert values.
Consider table STUDENT having attributes like ID, NAME, CITY, as discussed in the previous example. Now to add values to it, we are going to use the table.
INSERT INTO STUDENT (ID, name, city) SELECT * from new_student;
See the following output.
In the parentheses of table student, we have given the column name in the same order which was discussed in the table in the previous example. Now when we have executed a select * statement, then the values from the new_student table get copied to the student table.
The order should be the same as that of the student table if you are affecting the whole table.
It is not necessary that all rows should be executed, you can also insert specific rows using where condition and also specific columns which you want.
#Using UNION ALL keyword
Insert into table (column1, column2, …., column n) SELECT values UNION ALL SELECT values_1 UNION ALL ….;
- The table is the name of the table.
- Columns are the name of the columns.
- Values are the set of data you want to insert.
- UNION ALL is the keyword.
Consider table STUDENT having attributes like ID, NAME.
Now to add values use the following query.
Insert into student (ID, Name) Select 1,'Shubh' UNION ALL Select 2,'Sandeep' UNION ALL Select 3,'Shouvik';
Here values are inserted in the same order as mentioned in parentheses. UNION ALL is used for merging all the values in rows.
Finally, SQL Insert Multiple Rows | Insert Multiple Rows In SQL is over.