• I have somewhat found what's going on here. The two tables i'm talking here let's say TableA & TableB. The column name of the identity column is row_num_id. As part of our application process, the data gets loaded from a file to our staging area and after data cleansing stored in a table say Table_Initial. This table Table_Initial has same structure as TableA including that identity column. Now we switch the partition from Table_Initial to TableA. And here what I got to know is that in this scenario identity column allows same ids to be inserted in TableA. It somehow changes the identity column config for this table.

    Now when a part of data is getting loaded from TableB to TableA, then it checks the identity column. And because it got modified probably to a lower number than the current maximum value, it tries to insert data with already existing value and flags error, Hope i'm not confusing you guys with the description of scenario. Now why partition switch can cause identity reseed, thats the question.