Multi Insert in a Single Query

Multi Insert in a Single Query

Table Name: sample_table

Field Type
X1 Int(11)
X2 Varchar(100)



In MYSQL

INSERT INTO sample _table (x1, x2) VALUES (‘1’,’sample’), (‘2’,’sample1’);

In SQL server

INSERT INTO sample _table (x1,x2) SELECT ‘1’ as x1,’sample’ as x2 UNION ALL SELECT ‘2’ as x1,’sample1’ as x2

In MS Access

INSERT INTO sample _table (x1,x2) SELECT * FROM (SELECT ‘1’ as x1,’sample’ as x2 FROM onerow union all SELECT ‘2’ as x1,’sample1’ as x2 FROM onerow )

Onerow table is a temporary table. It should have only one data row.

The table should not have more than one row. It affects insertion.

If onerow table has multiple data rows then same records inserted in multiple times in the sample table.

If one row table has 2 data rows then, the result of the below sub query

SELECT ‘1’ as x1,’sample’ as x2 FROM onerow union all SELECT ‘2’ as x1,’sample1’ as x2 FROM onerow

Have duplicate data rows. So it cause the duplicate data insertion

Table name: one row

Field Type
X1 bool


Comparison between Single and Multiple Queries

Executing Multiple Queries in a loop Vs single Query to insert Multiple Records


Time Comparison








Memory Comparison






In forming query string, it takes lot of memory. Here is the memory comparison of single & multiple queries