Keep Identity Table Hint in SQL Server

The keep identity table hint is used when inserting data into a table using select into or insert into …. Select statement. It ensures that identity values from the source table are preserved rather than being reassigned in the target table.

Normally when we do bulk insert data into a table with an identity column, SQL server igneous the incoming identity values and generates new one. The Keep identity hints SQL Server to use the identity values from our source data instead.

Let’s see the example demo

Here we are creating two table one is SourceTable and another is DestinationTable.

create table SourceTable ( Product_id bigint identity(1,1) primary key, Product_name varchar(50)  null, Price decimal(18,10) null ) create table DestitionTable ( Product_id bigint identity(1,1) primary key, Product_name varchar(50)  null, Price decimal(18,10) null )

 Both tables created successfully.

Now inserting some records in SourceTable

insert into SourceTable(Product_name,Price) values ('Pen',10),('Copy',100),('Book',200),('Pencil',5)

Inserted successfully. See the records.

Lets delete these records and again insert these records.

delete from SourceTable insert into SourceTable(Product_name,Price) values ('Pen',10),('Copy',100),('Book',200),('Pencil',5)

See the records

Now we want to insert these records into destination table with the same product id. Let see

insert into DestitionTable(Product_id,Product_name,Price) select Product_id,Product_name,Price from SourceTable

We are getting the below error.

If we skip the Product_id then in the destination id will start from 1. See below.

insert into DestitionTable(Product_name,Price) select Product_name,Price from SourceTable

Record inserts successfully. See the records in both tables.

Both IDs are not match.

To achieve this we need to either use Keep identity table hint in the destination table or we need to use set IDENTITY_INSERT to OFF.

In this demo we are using the table hint.

Before using just, we are dropping the destination table and recreating this table again.

drop table DestitionTable create table DestitionTable ( Product_id bigint identity(1,1) primary key, Product_name varchar(50)  null, Price decimal(18,10) null )

Now we are inserting the records into the destination table using the table hint.

insert into DestitionTable  WITH (KEEPIDENTITY) (Product_id,Product_name,Price) select Product_id,Product_name,Price from SourceTable

 

See the records in the both table.

When we are using keep identity, we must explicitly include the identity column in our column list. It is generally used with bulk insert or when we are using BCP.

Be careful when preserving identity values, it can lead to conflict if the target table already has data with those identity values.

We need appropriate permission to use this hint (Required Alter permission on the table).