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.
Happy new Year 2015.