reset Identity column values after DB refresh

Mudit Gupta 81 Reputation points
2023-05-16T17:13:50.5033333+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Vahid Ghafarpour 20,030 Reputation points
    2023-05-16T17:42:35.88+00:00

    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,


  2. Vahid Ghafarpour 20,030 Reputation points
    2023-05-16T17:44:40.5633333+00:00

    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.

    0 comments No comments

  3. Guoxiong 8,201 Reputation points
    2023-05-16T20:39:33.5166667+00:00

    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.


  4. LiHongMSFT-4306 24,931 Reputation points
    2023-05-17T02:52:28.6633333+00:00

    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.

    0 comments No comments

  5. Olaf Helper 42,576 Reputation points
    2023-05-17T04:38:08.4166667+00:00

    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.

    0 comments No comments