Data modification statements

INSERT statement

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(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.

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', 599);

INSERT INTO Printer_Inc (model, color, type, price) 
VALUES (3111, 'y', 'laser', 599);

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" relationship 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 };

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model, color, type, price) 
VALUES (15, 3111, 'y', 'laser', 599);

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);

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(model, color, type, price) 
VALUES (3111, 'y', 'laser', 599);

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.