Introduction
SQL is a complete data manipulation language that is used not only for database queries, but also to modify and update data in the database. Compared to the complexity of the SELECT statement, which supports SQL queries, the SQL statements that modify and create database contents are somewhat simple. However, database updates pose some challenges for a DBMS beyond those presented by database queries. The DBMS must protect the integrity of the stored data during changes, ensuring that only valid data is introduced into the database. The DBMS must also coordinate simultaneous updates by multiple users, ensuring that the users and their changes do not interfere with one another.
Means the INSERT statement adds new rows of data to a table.
A new row of data is typically added to a relational database when a new entity is represented by the row "appears in the outside world". So for example, in the table presented in the SELECT example, if a new "customer" began purchasing from our company, we must add a new row to the CUSTOMER table to store the customer's information. If they were to make an order from our range of supplies then we might add a new row to the ORDERS table to store a record that this order was placed. In either case, the new row is added to maintain the database as an accurate model of the real world. The smallest unit of data that can be added to a relational database is a single row.
Syntax
Insert into table_Name (columl list) values (Values for column).
In general, an SQL-based DBMS provides three ways of adding new rows of data to a database:
- single-row INSERT
This statement adds a single new row of data to a table.
- multi-row INSERT
This statement extracts rows of data from another part of the database and adds them to a table. It is commonly used in end-of-month or end-of-year processing when "old" rows of a table are moved to an inactive table.
- bulk load
This utility adds data to a table from a file that is outside of the database. It is commonly used to initially load the database or to incorporate data downloaded from another computer system or collected from many sites.
- Single Row Insert statement
The single-row INSERT statement adds a new row to a table. The INTO clause specifies the table that receives the new row (the target table) and the VALUES clause specifies the actual data values that the new row will contain.
The column list indicates which data value goes into which column of the new row.
syntax
INSERT INTO employee(ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE)
VALUES (111,'vithal','wadje','[email protected]','India','+914545455454')
Note that the 111 is not surrounded by ' symbols because these are used when dealing with character types.
The ID in this situation is stored as an integer type. Conceptually, the INSERT statement bulds a single row of data that matches the column structure of the table, fills it with the data from the VALUES clause, and then adds the new row to the table.
The rows of the table are unordered, so there is no notion of inserting the row "at the top" or "at the bottom" or "between two rows" of the table. After the INSERT statement, the new row is simply a part of the table.
A SELECT statement can be performed on the table later, may include the new row, but it may appear anywhere among the rows of query results.
Single-entry INSERTS are most commonly performed through interactive SQL on tables that don't grow very often. In practice however, data about a new customer, order, etc. are nearly always added to a database through a forms-oriented data entry program. When the data entry is complete and the form submitted, the application layer will generate the SQL required to perform the database elements. More on this later.
The purpose of the column list in the INSERT statement is to match the data values in the VALUES clause with the columns that are to receive them. The list of values and the list of columns must both contain the same number of items, and the data type of each value must be compatible with the data type of the corresponding column, or an error will occur. As a convenience, SQL allows you to omit the column list from the INSERT statement, in the situation where all columns are being inserted. When the column list is omitted, SQL automatically generates a column list consisting of all columns of the table, in left-to-right sequence.
Using this shortcut, the previous INSERT statement could be rewritten equivalently as:
INSERT INTO employee VALUES
(111,'vithal','wadje','[email protected]','India','+914545455454')
One important factor here is that when you omit the column list, the NULL keyword must be used in the values list to explicitly assign NULL values to columns, as shown in the example.
In addition, the sequence of data values must correspond exactly to the sequence of columns in the table. When the column names are explicitly defined in the INSERT statement, it automatically assigns a NULL value to any column whose name is missing from the column list in the INSERT statement.
So therefore, the following are both correctly syntaxed SQL which perform the same effect:
INSERT INTO employee
VALUES
(111,'vithal','wadje','[email protected]','India',NULL)
INSERT INTO employee(ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE)
VALUES (111,'vithal','wadje','[email protected]','India','+914545455454')
Most database systems, during the generation of the table structure allow certain columns to be set as NOT NULL, which means that any attempt to omit data from that column when inserting rows, will result in an SQL error.
- Multi Row Insert Statement
This form of the INSERT statement adds multiple rows of data to its target table. In this form of the INSERT statement, the data values for the new rows are not explicitly specified within the statement text. Instead, the source of new rows is a database query, specified in the statement.
Syntax
As an example, let us consider the situation where we wish to split up our employee table into regionalised tables. So if we wanted to populate a table called tmptable we could copy the information in the following way:
INSERT INTO tmptable
(ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE)
SELECT ID, SURNAME, FIRSTNAME, EMAIL, COUNTRY, PHONE
FROM employee WHERE COUNTRY='India'
Using the shorthand discussed into the single-row discussion above, we can write this SQL statement in a shorter form:
INSERT INTO tmptable SELECT * FROM employee
WHERE COUNTRY='India'
The INSERT statement identifies the table to receive the new rows and the columns to receive the data, just like the single-row INSERT statement. The remainder of the statement is a query that retrieves data from the employee table. Conceptually, SQL first performs the query against the ORDERS table and then inserts the query results, row by row, into the tmptable table.
There were a number of restrictions regarding multi-row INSERT statements made in the SQL1 standard:
- The query cannot contain an ORDER BY clause. It's useless to sort the query results anyway, because they're being inserted into a table that is, like all tables, unordered.
- The query results must contain the same number of columns as the column list in the INSERT statement (or the entire target table, if the column list is omitted), and the data types must be compatible, column by column.
- The query cannot be the UNION of several different SELECT statements. Only a single SELECT statement may be specified.
- The target table of the INSERT statement cannot appear in the FROM clauses of the query or any subqueries that it contains. This prohibits inserting part of a table into itself.
The first two restrictions are structural, but the latter two were included in the standard simply to avoid complexity. As a result, these restrictions were relaxed in the SQL2 and subsequent standards.
The standards now allow UNION and join operations and expressions in the query. They also allow various forms of "self-insertion", where the source table for the data to be inserted and destination table are the same.
Data to be inserted into a database is often downloaded from another computer system, or collected from other sites and stored in a sequential file. To load the data into a table, you could write a program with a loop that reads each record of the file and uses the single-row INSERT statement to add the row to the table. However, the overhead of having the DBMS repeatedly execute single-row INSERT statements can be quite high.
For this reason, all commercial DBMS products include a bulk load feature that loads data from a file into a table at high speed. The ANSI/ISO standard does not address this function, and it is usually provided as a standalone utility program rather than as part of the SQL language. Each vendor's utility provides a different set of features, functions and commands.
Summary
I hope this article is useful for all readers. If you have any suggestion then please contact me.