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.
Recommended Articles
Top courses in SQL
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 Business.