How to Insert Multiple Rows in SQL?

As 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.

• When inserting a single record using the VALUES keyword the syntax for the SQL INSERT statement is:

INSERT INTO table

(column1, column2, … )

VALUES

(expression1, expression2, … );

• When inserting multiple records using a SELECT statement the syntax for the SQL INSERT statement is:

INSERT INTO table

(column1, column2, … )

SELECT expression1, expression2, …

FROM source_tables

WHERE conditions;

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

• table is the table to insert the records into.
• column1, column2 are the columns in the table to insert values.
• expression1, expression2 are the values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
• source_tables is the source table when inserting data from another table.
• conditions are conditions that must be met for the records to be inserted.

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:

• A SELECT clause: This specifies the columns which contain the values to be matched against.
• A FROM clause which specifies the TABLE containing the columns listed in the SELECT clause.

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 is a mismatch between the number of columns and the number of values
• If the datatype of an inserted value is a mismatch with the column of the target table
• There is a size mismatch between a value and its target column.
• When a column is defines as not NULL and the insertion value is NULL.

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.