SQL insert multiple rowsAs an SQL database programmer and developer you have often asked yourself, “Can I insert multiple rows into a single table through a single SQL statement?” One way to insert any value into a table is to write multiple insert statements. This is not only boring and tedious but also time consuming. There must be smarter ways to insert multiple rows than to repeat the same syntax several times. We will discuss here how to accomplish this task in a more efficient way.

Before we elaborate on the methods, you can check out the courses SQL Database for Beginners and Learn SQL in 6 days which will guide you on how to perform operations on databases, how to perform Insert and Deletes in MS SQL, and how to insert data into tables. If you are new to writing SQL queries and want to learn how to build applications or generate business reports using SQL, then Introduction to SQL Training is a perfect match for you.

Introduction

To give you a short introduction, SQL or Structured Query Language is the standard language used to communicate with relational database management systems, such as Oracle, Microsoft SQL Server, Sybase, Informix, and so on. With SQL, you can build databases, enter data into the database, manipulate data, and query the database data. The output or results from the query are often used in analysis or to make business decisions. SQL is a simple, friendly, English-like language that is relatively easy to learn and is being extensively used by database users and developers all over the world.

SQL INSERT Statement

In SQL, an INSERT statement adds one or more records and rows to any single table in a relational database.

To start with let us look at the INSERT statement options in SQL.

INSERT INTO table

(column1, column2, … )

VALUES

(expression1, expression2, … );

INSERT INTO table

(column1, column2, … )

SELECT expression1, expression2, …

FROM source_tables

WHERE conditions;

Explanation of the parameters or arguments used in the above syntax:

INSERT and SELECT

INSERT and SELECT statements are used to add rows to a table. The SELECT statement retrieves or extracts information from tables in the database. Using the SELECT statement, you can choose the rows in a table that you want returned from a query by specifying values to match the rows that you want to display. The INSERT statement with a nested SELECT statement helps a table to be quickly populated with one or more rows from the result set of the SELECT statement.

In its simplest form a SELECT statement must contain the following:

When using INSERT…SELECT between a target and a source table, it is mandatory to ensure that the datatypes returned by the SELECT statement are compatible with the datatypes in the target table.

Methods to Insert Multiple Values into a Single Table

Here we have discussed three different methods to insert multiple values into a single table.

Method 1:  Traditional INSERT…VALUE Method

Syntax:

INSERT INTO <table_name> ( Column1, Column2 )

VALUES ( Value1, Value2 ), ( Value3, Value4 )

Method 2: INSERT with SELECT

Syntax:

INSERT INTO <table_name> ( Column1, Column2 )

SELECT expression1, expression2, …

Method 3: INSERT with SELECT UNION ALL 

Syntax:

INSERT INTO <table_name>  (column1, column2)

SELECT  ‘First’ ,1

UNION ALL

SELECT  ‘Second’ ,2

UNION ALL

SELECT  ‘Third’ ,3

When do INSERT Statements Fail?

Some of the most common errors which occur while executing INSERT statement:

There are a large number of SQL enhancements in Oracle 9i. Multitable inserts are possible here, where a single INSERT INTO and SELECT statements allow to conditionally, or unconditionally insert data into multiple tables. This statement reduces table scans and PL/SQL codes necessary for performing multiple conditional inserts which appear in previous versions.

Conclusion

Most problems with INSERT statements occur as the programmer does not know the target table very well. Make sure you understand the target table or view well before writing elaborate INSERT statements. While you keep exploring the INSERT statements, you can explore SQL Queries 101 to understand how to write basic SQL queries and other code statements. If you want to find out more on Oracle SQL and Oracle PL/SQL Programming then browsing through Introduction to Oracle SQL or Oracle PL/SQL Tutorial – A Comprehensive Training Course will be of great help.

Top courses in SQL

SQL and Data Visualization - The Complete Bootcamp
Raffi Sarkissian | SQL | PostgreSQL | Metabase
4.6 (21)
SQL - Beyond The Basics
Infinite Skills
4.4 (460)
The Complete SQL Bootcamp 2020: Go from Zero to Hero
Jose Portilla
4.7 (74,990)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (43,501)
Bestseller
SQL for Data Analysis: Weekender Crash Course for Beginners
A Course You'll Actually Finish, David Kim, Peter Sefton
4.3 (11,348)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.6 (5,903)
SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL
Kirill Eremenko, Ilya Eremenko, SuperDataScience Team
4.4 (3,499)
Bestseller
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.4 (5,331)
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.5 (1,182)
Bestseller

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo