SQL data manipulation language

Data modification statements

The INSERT statement adds new rows to a table. In so doing, the column values may be literal constants or be formed from result of subquery execution. In the first case, one INSERT statement adds only one row; in the second case, one INSERT statement adds as many rows as the subquery returns. 

Syntax

INSERT INTO < name of TABLE >[(< name of COLUMN >,...)]
{VALUES (< value of COLUMN >,…)}
| (< SELECT statement >) 
| {DEFAULT VALUES}

As may be seen, the column list is optional. When the column list is absent, the list of column values must be full, i.e. the values for all columns must be present. In so doing, the order, in which the values follow, must correspond exactly to the column order specified the CREATE TABLE statement for the table rows to be added. Moreover, each value must be of the same or compatible data type as the type specified for the corresponding column in the CREATE TABLE statement. AS an example, let us consider the adding a row to the Product table created by the following statement CREATE TABLE:

CREATE TABLE product 
(
 maker char (1) NOT NULL,
 model varchar (4) NOT NULL,
 type varchar (7) NOT NULL 
)

Let we add to the above table the PC model of 1157 from the maker B. This can be accomplished by the following statement:

INSERT INTO Product 
VALUES ('B', 1157, 'PC');

With defining the column list, we can change a "natural" order of columns:

INSERT INTO Product (type, model, maker) 
VALUES ('PC', 1157, 'B');

This is seemingly excess opportunity that makes writing more cumbersome. However it can be very useful if columns have default values. Consider a table of the following structure:

CREATE TABLE product_D 
(
 maker char (1) NULL,
 model varchar (4) NULL,
 type varchar (7) NOT NULL DEFAULT 'PC'
)

Note that all above columns have default values (the first two - NULL, and the last - type column - 'PC'). Now we could write: 

INSERT INTO Product_D (model, maker) 
VALUES (1157, 'B');

In this case when adding row, the absent value will be replaced by the default value - 'PC'. Note, if neither a default value nor the NOT NULL constraint definition was specified for a column in the CREATE TABLE statement, NULL is implied as default value. 

This raises the question of whether to use default values but, nevertheless, not specify the column list? The answer is positive. To do this, we can use DEFAULT keyword instead of specifying a value explicitly:

INSERT INTO Product_D 
VALUES ('B', 1158, DEFAULT);

As all the columns have default values, we could add a row with default values by the following statement:

INSERT INTO Product_D 
VALUES (DEFAULT, DEFAULT, DEFAULT);

However for this case there is the special DEFAULT VALUES keyword (see syntax), thus we can rewrite the above statement as follows

INSERT INTO Product_D DEFAULT VALUES;

Note that when inserting a row into a table, all restrictions for this table will be checked. These restrictions are primary key or unique constraints, CHECK or FOREIGN KEY constraints. If any of these restrictions are violated the addition of a row will be denied.

Let us consider the case of subquery used in the following example:

Add to the Product_D table all the rows from the Product table, which refer to the models of PC (type = 'PC').

Since the needed values are in a table we should not add them by typing, but by using a subquery:

INSERT INTO Product_D 
SELECT * 
FROM Product 
WHERE type = 'PC';

Usage the "*" symbol in the subquery is warranted here because the column orders and types are identical for both tables. If this is not so, column list should be used either in the INSERT clause or in the subquery or in the both, assuming the column order is consistent:

INSERT INTO Product_D(maker, model, type) 
SELECT * 
FROM Product 
WHERE type = 'PC';

or

INSERT INTO Product_D 
SELECT maker, model, type 
FROM Product 
WHERE type = 'PC';

or

INSERT INTO Product_D(maker, model, type) 
SELECT maker, model, type 
FROM Product 
WHERE type = 'PC';

Here, as before, it is not required to list all columns if available default values are to be used instead. For example:

INSERT INTO Product_D (maker, model) 
SELECT maker, model 
FROM Product 
WHERE type = 'PC';

In this case, the default value - 'PC' - will be inserted into the type column of the Product_D table for all added rows.

When using subquery with predicate, it should be noted that only those rows will be added for which the predicate evaluates to TRUE (not UNKNOWN!). In other words, if the type column in the Product table adopts NULL value and NULLs are in any rows, these rows will not be added to the Product_D table.

To overcome the restriction of inserting a single row with VALUES clause, we can use an artificial trick by forming by a subquery with the clause UNION ALL. If you need to add multiple rows with a single INSERT statement, you can write:

INSERT INTO Product_D 
SELECT 'B' AS maker, 1158 AS model, 'PC' AS type
UNION ALL
SELECT 'C', 2190, 'Laptop'
UNION ALL
SELECT 'D', 3219, 'Printer';

Using the UNION ALL clause is preferable to the UNION clause, even though duplicates of rows are not checked. This is because checking of duplicate with the UNION clause is assured whereas the UNION ALL clause it is not.

It should be noted that the insert of several tuples by means of the row constructor is already implemented in  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2008. In view of this opportunity, last query can be rewritten in the form of:

INSERT INTO Product_D VALUES
('B', 1158, 'PC'),
('C', 2190, 'Laptop'),
('D', 3219, 'Printer');

Note that MySQL supposes one more non-standard syntax structure which is carrying out an insert of a row in a table in the style of UPDATE statement:

INSERT [INTO] < TABLE name >
SET {< COLUMN name > = {< expression > | DEFAULT}},...

INSERT INTO Product
SET maker = ' B ',
     model = 1157,
     type = ' PC ';

Content:

(6.1.1.) Adding rows to a table with identity column