Insert SQL – Means to Insert Records

SQL or Structured Query Language, is the standard language used by software companies and program developers to communicate with a relational database. SQL is used to express your needs to the database. If you want to request specific information from within a database, you do it in the form of a query in SQL. As a database developer one of your main aims is to insert records and INSERT statement in SQL is the appropriate query to use.

In case you are looking to get acquainted with relational databases and data management at an elementary level then Database Management System, Microsoft SQL Database Fundamentals will introduce you to the preliminary concepts. 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 the perfect match for you.

 SQL INSERT Statement

In SQL, an INSERT statement adds one or more records to any single table in a relational database. The INSERT statement may also be used to add rows to the base table, view, partition, subpartition, or object table. It also supports additional extensions such as inserting records into many tables at once and conditional inserts.  SQL Database for Beginners will help you get acquainted with the correct syntax and formats of INSERT statement.

Options with INSERT

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

INSERT INTO

INSERT INTO – Single Column Table Or View: INTO statement is used to add one or more records to one single table. The number of columns must be equal to the number of values.

Basic syntax of an SQL Insert INTO statement is:

INSERT INTO Table_Name

VALUES (‘value 1’, ‘value 2’, [NULL] );

The columns to be inserted in the table are specified in the VALUES list. The values inserted must be enclosed by single quotation marks for character and date/time data types. Single quotations are not required for numeric data types or NULL values. A value should be present for each column and the values should be in the same order as the columns to be listed in the table.

Example: INSERT INTO address_book (name, house number) VALUES (‘Robin Brown’, ’202′)

INSERT Multiple rows in a table: This syntax is used to insert multiple rows at a time through a single SQL statement.

Syntax:

INSERT INTO <table_name>

(<comma_separated_column_name_list>)

VALUES

(<comma_separated_value_list>);

Example: INSERT INTO address_book VALUES (‘Robin Brown’, ’202′), (‘John Roy’, ’104′);

Combining INSERT and SELECT Statements

Combination of INSERT INTO and SELECT statements allow you to conditionally, or unconditionally insert data into multiple tables. This statement reduces table scans and reduces time required for performing multiple conditional inserts.

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.

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

Example of INSERT INTO SELECT Statement

Let’s assume we have two tables – one containing vendor information and the other containing the vendor order details.

Table Vendor_Information

Column Name                                 Data Type

Vendor_Name                                  char(50)

Order                                                 float

Ord_Date                                        datetime

Table Order_Data

Column Name                                 Data Type

Vendor_Name                                  char(50)

Order                                                 float

Ord_Date                                         datetime

Product_ID                                     integer

Table Order_Data has detailed order information placed with a particular vendor, while table Vendor_Information keeps a summary of the order placed with each vendor on a daily basis.

To move data from Order_Data to Vendor_Information, we would type in:

Syntax:

INSERT INTO Vendor_Information (Vendor_Name, Order, Ord_Date)

SELECT Vendor_Name, SUM(Order), Ord_Date

FROM Order_Data

GROUP BY Vendor_Name, Ord_Date;

After the INSERT INTO statement we specified the order of the columns to be displayed in the output table, for example, the first column should be Vendor_Name, second column Order, and the third column Ord_Date.

INSERT with Conditions or WHEN:

Syntax:

INSERT

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

ELSE

INTO <table_name> (<column_list>)

VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

INSERT ALL 

Without the WHEN clause INSERT ALL performs all inserts unconditionally.

Syntax:

INSERT ALL

INTO <table_name> VALUES <column_name_list)

INTO <table_name> VALUES <column_name_list)

…

<SELECT Statement>;

INSERT ALL WHEN

WHEN specifies the conditions for inserting.

Syntax:

INSERT ALL

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

ELSE

INTO <table_name> (<column_list>)

VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

INSERT FIRST WHEN

This tells SQL to evaluate the WHEN clauses in a certain order. When it finds the first true expression, the database executes the corresponding INTO clause and skips all other WHEN clauses.

Syntax:

INSERT FIRST

INTO <table_name> VALUES <column_name_list)

INTO <table_name> VALUES <column_name_list)

…

<SELECT Statement>;

INSERT WITH CHECK OPTION

WITH CHECK OPTION  indicates that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.

Syntax:

INSERT INTO (

<SQL_statement> WITH CHECK OPTION)

VALUES

(value_list);

INSERT With Returning Clause

Used for inserting into a table with a return clause. The RETURNING clause is not allowed with multitable insert operations.

Syntax:

INSERT INTO <table_name>

(column_list)

VALUES

(values_list)

RETURNING <value_name>

INTO <variable_name>;

Conclusion

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. While you keep exploring the INSERT statements, you can check out Practical SQL Skills from Beginner to Intermediate.