Identitiy Insert On SQL Server

dbtut

6 years ago

You need to know about Primary Key and Foreign key before you can read this article. You can read the article “What is Primary Key and Foreign Key“.

In a column with identity column property set, the values in the column automatically increase.

For example, you created a unique column named ID in the table. You want this column’s value to increase automatically 1 value each time a record is added to the table. You can do this by defining the identity property on this column.

If the table has a column with identity property set, identity column is not specified in the INSERT statement when you want to insert a record into the table. The identity column’s value increases automatically.

You can define the identity property when creating a table or later. The identity property cannot be set for an existing column in the current table. We will do this in another way. I will address this method later in the article.

With the help of the following script, let’s set the identitiy property for the ID column in the following properties when creating the table.

auto increment value will start from 1
auto increment value will increase one by one.

CREATE TABLE myIdentity 
( 
 ID int IDENTITY(1,1), 
 City varchar (200), 
 Country varchar (200)
);

CREATE TABLE myIdentity

(

ID int IDENTITY(1,1),

City varchar (200),

Country varchar (200)

);

If you change the create table script like below, auto increment value will start from 3 and will increase two by two.

CREATE TABLE myIdentity
(
ID int IDENTITY(3,2),
City varchar (200),
Country varchar (200)
);

CREATE TABLE myIdentity

(

ID int IDENTITY(3,2),

City varchar (200),

Country varchar (200)

);

Let’s add a record to this table with the help of the following script. As you can see in the script below, there are 3 columns in our table, but there are 2 columns in the insert statement.

INSERT INTO [dbo].[myIdentity]([City],[Country]) VALUES ('Ankara','Turkey')
GO
INSERT INTO [dbo].[myIdentity]([City],[Country]) VALUES ('Istanbul','Turkey')

INSERT INTO [dbo].[myIdentity]([City],[Country]) VALUES ('Ankara','Turkey')

INSERT INTO [dbo].[myIdentity]([City],[Country]) VALUES ('Istanbul','Turkey')

You can see which values are assigned to the ID column by selecting from the table after insert operation.

Select * From dbo.myIdentity

1	Select * From dbo.myIdentity

As you can see, the value for ID was 1 for Ankara and 2 for Istanbul.

Sometimes we need to move our tables to another database. In such cases, many methods can be used.

For example, by taking the script of our table, let’s try to transfer the records to the table we just created. When transferring, we need to preserve the existing values in the Identitiy column.

If Identitiy Column does not exist, we can do this with the help of the following script.

Insert Into [myIdentityYeni]
Select * From myIdentity

1 2	Insert Into [myIdentityYeni] Select * From myIdentity

But since myIdentityYeni has a column with identity property, it will return with the following error.

CREATE TABLE myIdentityYeni 
( 
 ID int IDENTITY(1,1), 
 City varchar (200), 
 Country varchar (200)
); 
Insert Into [myIdentityYeni] (ID,City,Country)
Select * From myIdentity

CREATE TABLE myIdentityYeni

(

ID int IDENTITY(1,1),

City varchar (200),

Country varchar (200)

);

Insert Into [myIdentityYeni] (ID,City,Country)

Select * From myIdentity

Msg 544, Level 16, State 1, Line 7

Cannot insert explicit value for identity column in table ‘myIdentityYeni’ when IDENTITY_INSERT is set to OFF.

In order to do what we need, we need to change the query as follows.

SET IDENTITY_INSERT [myIdentityYeni] ON
GO
Insert Into [myIdentityYeni] (ID,City,Country)
Select * From [myIdentity]
GO
SET IDENTITY_INSERT [myIdentityYeni] OFF

SET IDENTITY_INSERT [myIdentityYeni] ON

Insert Into [myIdentityYeni] (ID,City,Country)

Select * From [myIdentity]

SET IDENTITY_INSERT [myIdentityYeni] OFF

After running the query as above, our insert operation will be completed successfully.

After creating a table, the identity property cannot be set for the current column. If you have such a need, you can recreate the table with the identity property and transfer the records from the current table to the new table by the above method.