In the INSERT INTO statement tutorial, we have seen how to enter new records into the database tables. You may use column names with VALUES or without using the column names for inserting records.
In many scenarios, you may require entering multiple rows by using the single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.
First way – Separating VALUES part by a comma
As using simple INSERT INTO statement, we used this statement for entering single record (for our example employee table):
INSERT INTO sto_employees (id, emp_name, emp_salary, emp_age, join_date) VALUES (13, 'Shaan', 5000.00, 35, '2016-12-13');
For entering multiple records just separate the VALUES by commas. See the example below:
INSERT INTO sto_employees (id, emp_name, emp_salary, emp_age, join_date) VALUES (14, 'Jumma', 4500, 35, '2017-09-15'), (15, 'Hareem', 3600, 26, '2018-05-29'), (16, 'Rudaba', 3200, 24, '2016-11-04');
The above query will enter multiple records into the sto_employees table. You can see, after the VALUES keyword, the first row values are enclosed in parenthesis. This is followed by a comma and then second row values and so on.
This single query should enter records in MS SQL Server as well as MySQL database table.
Insert multiple records without specifying the column names
Multiple records can also be entered without providing the column names (just like in the INSERT INTO command with single record).
In that case, you have to provide all column name values in the same sequence as in the actual physical table. Otherwise, an error may be raised if fewer values are provided than the number of columns in the table or if data type mismatch.
The following query inserts three records in the employee’s table without using the column names:
INSERT INTO sto_employees VALUES (17, 'Mike', 4500, 35, '2017-09-15', '012-1454-457474'), (18, 'Jameel', 3600, 26, '2018-05-29', '005-5774-457474'), (19, 'Janaan', 3200, 24, '2016-11-04', '001-4747-457474');
Again, this query is tested on MS SQL Server as well as MySQL database.
Second Way – Using INSERT INTO & SELECT statements
The combination of INSERT INTO and SELECT statements also allows you entering multiple records by single statement. However, this is used to copy data from one table to another.
The source table (from where you are copying data) is specified in the SELECT statement. The target table (where you are inserting records) is specified in the INSERT INTO command.
The following example shows an example query:
INSERT INTO employees_sub SELECT * FROM sto_employees;
In that case, the complete table rows of sto_employees will be inserted into the employee_sub table. The columns sequence and specification must be the same (names can be different).
You may also use WHERE clause in SELECT statement for inserting specific rows. I have written a detailed guide of using this option in INSERT INTO with SELECT for copying data.
Third way: The UNION Operator
In the third way of inserting multiple rows by single query is using the UNION operator. The UNION operator is basically used to combine the results of SELECT statements.
So, again this technique uses INSERT INTO and SELECT statement, however rather than copying the data from one table to another, you may insert data with independent values (just like the first way).
The INSERT INTO specifies the target table while in the SELECT statements, values are specified. Multiple SELECT statements are combined by the UNION operator as shown in the query below:
INSERT INTO [sto_employees] ([id] ,[emp_name] ,[emp_age] ,[emp_salary] ,[join_date] ,[phone]) SELECT 20, 'Tim', 45, 5500, '2017-09-15','044-4745-145745' UNION ALL SELECT 21, 'Tony', 47, 6000, '2017-09-15','092-1545-474184' UNION ALL SELECT 22, 'Tania', 34, 4500, '2017-09-15','056-2455-454745';
The result-set before and after executing this query (for demo only):
Note: This query is tested on MS SQL Server database.
