Data Manipulation Language (DML) besides of the SELECT statement that retrieves information from databases includes also statements modifying data state. These statements are:
| INSERT | Inserts rows into database table |
| UPDATE | Changes values in columns of database table |
| DELETE | Deletes rows from database table |
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 <table name>[(<column name>,...)]
{VALUES (< column value>,)}
| <query expression
| {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 [dbo].[product] (
[maker] [char] (1) NOT NULL , [model] [varchar] (4) NOT NULL , [type] [varchar] (7) NOT NULL ) |
It is needed to add to the above table the model 1157 of 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.
|
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.
Many commercial products allow using auto incrementable columns, i.e. columns that form their values automatically when adding new rows. Such columns are widely used as primary keys in tables by virtue of uniqueness of these values. A typical example of that column is sequential counter, which generates a value greater by 1 than previous value (the value obtained when adding previous row).
Below, there is example of creating a table with identity column (code) in MS SQL Server.
|
CREATE TABLE [Printer_Inc] (
[code] [int] IDENTITY(1,1) PRIMARY KEY , [model] [varchar] (4) NOT NULL , [color] [char] (1) NOT NULL , [type] [varchar] (6) NOT NULL , [price] [float] NOT NULL ) |
Autoincrementable column is defined trough IDENTITY (1, 1) function where the first parameter (seed value) is the value to be assigned to the first row in the table, and the second is the increment to add to the seed value for successive rows in the table. So, in that example, the first inserted row obtains in the code column the value of 1, the second row - 2 and so on.
Since the value in the code column is formed automatically, the statement
| INSERT INTO Printer_Inc VALUES (15, 3111, 'y', 'laser', 2599); |
raises error, even though the table has not a row with the value of 15 in the code column. Thus we will not include that column when adding the row to the table just as we do when using default value, i.e.
|
INSERT INTO Printer_Inc (model, color, type, price)
VALUES (3111, 'y', 'laser', 2599); |
As a result, the information about model 3111 for color laser printer priced $2599 will be added to the Printer_Inc table. The value in the column code is 15 only accidentally. In the most cases, the specific value in an identity column is of no concern for this value has no sense as a rule; the first moment is uniqueness of the value.
Nevertheless, there are cases where a specific value needs to be inserted into autoincrementable columns. This takes place, for example, when existing data must be transferred into a new structure. In so doing, these data are in a "one-to-many" relation from the "one " side. Thus, arbitrary values are not allowed here. On the other hand, we want to use autoincrementable field later.
In the absence of autoincrementable columns in the SQL Standard, a single approach does not exist. Here, the realization in MS SQL Server. The statement
| SET IDENTITY_INSERT < table name > { ON | OFF }; |
turn off (ON value) or on (OFF value) auto increment use. Thus, to add a row with the value of 15 in the code column, we may write
|
SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model, color, type, price) VALUES (15, 3111, 'y', 'laser', 2599); |
Note that column list is necessary in this case, i.e. we can neither write:
|
SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc VALUES (15, 3111, 'y', 'laser', 599); |
nor
|
SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(model, color, type, price) VALUES(3111, 'y', 'laser', 2599); |
In the last case, missing value in the code column cannot be inserted automatically because auto increment is turned off.
It should be noted that numbering would be continued from the value of 16 if the value of 15 were the maximum in the code column. Clearly if the auto increment will be turned on: SET IDENTITY_INSERT Printer_Inc OFF.
At last, let us consider an example addition the data from the Product table to the Product_Inc table while conserving the values in the code column:
|
SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model,color,type,price) SELECT * FROM Printer; |
The following should be said of autoincrementable columns. Let the last value in the code column is 16. Then the row with that value is deleted. What is the value then in this column after adding a new row? Correctly, 17, i.e. the last value of counter is conserved despite of deletion of the row with the value of 16. So, the numbering will not be sequential after deletion and addition of rows. This is another reason for inserting the row with a given (missed) value in the autoincrementable column.
| Home | SELECT exercises (rating stages) | DML exercises | Developers |