How do I insert multiple rows in a single SQL query?

Richard C.

The Problem

If you want to insert many rows into a SQL table, you have to repeat INSERT INTO over and over in separate statements.

INSERT INTO Person VALUES (1, "Amir"); INSERT INTO Person VALUES (2, "Sofia"); INSERT INTO Person VALUES (3, "Aya"); ...

Is there a way to use a single statement and avoid repetition?

Assume the table we are working with has just Id and Name columns. This code works on SQL Server, MySQL, and PostgreSQL.


The Solution

The solution depends on which database server and version you are using. They each support different syntax.

SQL Server 2008 and Later

SQL Server allows you to enter up to 1000 rows in a single statement.

INSERT INTO Person (Id, Name) VALUES (1, 'Amir' ), (2, 'Sofia'), (3, 'Aya'), (4, 'Mateo'), (5, 'Leila'), (6, 'Yara'), (7, 'Ndidi'), (8, 'Santiago');

After 1000 entries, performance degrades, so Microsoft wants you to split your entries into separate transactions.

Earlier SQL Server Versions

If you’re using an earlier version of SQL Server, you can’t use this syntax. You have to use multiple INSERT statements.

Some answers online suggest using UNION ALL syntax to create a SELECT statement from which you INSERT. We don’t recommend this for performance reasons.

INSERT INTO Person (Id, Name) SELECT 1, 'Amir' UNION ALL SELECT 2, 'Sofia' UNION ALL SELECT 3, 'Aya';

Rather investigate the Microsoft BULK INSERT tool for large inserts.

BULK INSERT Person FROM '\\data\people.dat';

MySQL and PostgreSQL

MySQL and PostgreSQL also support modern syntax. This has been available since around 2006.

INSERT INTO Person (Id, Name) VALUES (1, 'Amir' ), (2, 'Sofia'), (3, 'Aya'), (4, 'Mateo'), (5, 'Leila'), (6, 'Yara'), (7, 'Ndidi'), (8, 'Santiago');

(Note that if you are trying this in for MySQL, you need to enter this code in the schema panel, not the query panel).

Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.