reset Identity column values after DB refresh

Question

Hello,

I do database refresh from Production to DEV. So, table data ( consisting of identity table) also gets copied over.

I have this requirement to not copy identity column values for one of the table. I am doing this by dumping table data into temp table, running dbcc checkident ( table, reseed,1). Truncating original table & inserting temptable data to original table.

Just checking if there's any better solution for this.

This is SQL Server 2016.

Thanks!

Answer 1

Hello Mudit,

You can consider using the SET IDENTITY_INSERT statement. This statement allows you to explicitly specify values for an identity column during an insert operation.

By disabling the identity property using SET IDENTITY_INSERT, you can insert explicit values into the identity column without interference from the auto-increment mechanism. Just make sure to enable the identity property back on after you're done with the insert.

This approach eliminates the need for creating a temporary table, resetting the identity column using DBCC CHECKIDENT, and truncating the original table. However, it's essential to be cautious and ensure data consistency and referential integrity when using this method.

Warm regards,

Answer 2

Hello Mudit,

Did you try the SET IDENTITY_INSERT statement. This statement allows you to explicitly specify values for an identity column during an insert operation.

Answer 3

Hello Mudit,

I do not think you need to truncate the original table and then copy data from the temp table to the original table. After you dump data into the temp table and reseed the identity column, you can directly rename the original table to other name, i.e., Table_Name_backup and then rename the temp table to the original table name. Finally You can drop the backup table if you think data in the table are good.

Answer 4

Hi @Mudit Gupta

This code reset all table's identity values to max value.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

I have this requirement to not copy identity column

Then you don't have to do any further action, also not to reseed the identity value.