How to insert value in Identity column ? TIP #80

 

Happy new year 2015 .

Suppose you have a source table and one destination table (Which is exact replica of source table) and you want to copy all the rows of source table into destination table. Now the challenging part here is that there is an identity column and in source & destination table and you want to insert same value of identity column in destination tables column.

Lets understand this by an example.

Suppose you have tblStudentSource table which have StudentId as primary key and with Identity feature

Now you have another table which is tblStudentDest with same column structure which tblStudentSource has

now you want to insert all the value of tblStudentSource into tblStudentDest with keeping the identity value.

In this case you have to write following command.

SET IDENTITY_INSERT  tblStudentDest ON;
INSERT INTO tblStudentDest (StudentId,FirstName,LastName,Course,detail) SELECT * FROM tblStudentSource
SET IDENTITY_INSERT  tblStudentDest OFF;

I hope this might help you somewhere.

Thanks

RJ

Happy new Year 2015.