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.
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:
Let we add to the above table the PC model of 1157 from the maker B. This can be accomplished by the following statement:
With defining the column list, we can change a "natural" order of columns:
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:
Note that all above columns have default values (the first two - NULL, and the last - type column - 'PC'). Now we could write:
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:
As all the columns have default values, we could add a row with default values by the following statement:
However for this case there is the special DEFAULT VALUES keyword (see syntax), thus we can rewrite the above statement as follows
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:
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:
Here, as before, it is not required to list all columns if available default values are to be used instead. For example:
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:
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:
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: