Insert a specific value into the Oracle ID column

You have some knowledge about the Oracle ID column. The following describes how to insert a specific value in the Oracle ID column. If you are interested in this, take a look.

Although you can use the value of the Oracle identity column and any value of the column for thousands of reasons, however, some people who work with you will insist on using continuous primary key words in the given table ). Then, when the invoice number is lost, they will be panic, afraid of being sued, cover up errors, or even worse.

To solve this problem, you can create a table with the Oracle ID column and fill it with some data rows:

 
 

  
  
-- Create a test table.  
  
  
CREATE TABLE TestIdentityGaps  
  
  
(  
  
  
ID int IDENTITY PRIMARY KEY,  
  
  
Description varchar(20)  
  
  
)  
  
  
GO  
  
  
-- Insert some values. The word INTO is optional:  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('One')  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Two')  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Three')  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Four')  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Five')  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Six')  
  
  
GO  
 
 

Delete several data rows:

 
 

  
  
DELETE TestIdentityGaps  
  
  
WHERE Description IN('Two', 'Five')  
 
 

When we write code, we know that the values "Two)" and "Five)" are lost. We want to insert two data rows to fill these gaps. Two simple INSERT statements cannot meet the requirements. However, they create primary keywords at the end of the sequence.

 
 

  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Two Point One')  
  
  
INSERT [INTO] TestIdentityGaps (Description) VALUES ('Five Point One')  
  
  
GO  
  
  
SELECT * FROM TestIdentityGaps  
 
 

You cannot explicitly set the value of the ID column either:

 
 

  
  
-- Try inserting an explicit ID value of 2. Returns a warning.  
  
  
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, 'Two Point One')  
  
  
GO  
 
 

To solve this problem, SQL Server 2000 uses IDENTITY_INSERT for settings. To forcibly insert a data line with a specific value, you need to issue a command to connect the specific inserted content to the end:

 
 

  
  
SET TestIdentityGapsON  
  
  
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, 'Two Point One')  
  
  
INSERT INTO TestIdentityGaps (id, Description) VALUES(5, 'Five Point One')  
  
  
GO  
  
  
SELECT * FROM TestIdentityGaps  
 
 

Now you can see that the new data row has been inserted with the specified primary key value.

Note: the setting of IDENTITY_INSERT can be used in a table in the database at any specific time. To fill the gaps in one or more tables, you must use specific commands to specify each table.

You can insert a specific value in a table with an ID column. To do this, you must first set the value of IDENTITY_INSERT to ON. If you do not have one, you will see an error message. Even if you set the value of IDENTITY_INSERT to ON, if you insert another existing value, you will still see the error message.

Oracle command line custom editor vi

Implementation of oracle command line Logon

Syntax for creating views in Oracle

Create tables and indexes in Oracle

This section describes Oracle partition indexes in detail.