Friday, March 02, 2012

How to insert rows into table with identity column

Imagine you have  two billion rows on employee table, you have to archive this table. After archiving you are going to insert back only 110 million rows to employee table from employee_temp, There is a column emp_id is identity, so how can you insert data back from archived tables.  Here you can find the tested solution , first you need to disable an indentity column to auto insert.

Step 1: Make sure to stop all process(application connection) against employee table
Step2 :Create a new table employee_copy from employee table
           Find max(emp_id) from employee eg: 2000000001and  RESEED this value to employee_copy
           DBCC CHECKIDENT (employee_copy , RESEED, 2000000001)
Step3:  Rename the orginal table using sp_rename 'employee' , 'employee_temp'
Step4: Rename the new table using sp_rename 'employee_copy' , 'employee'
Step5:  Insert the rows from employee_temp table to employee table

SET IDENTITY_INSERT employee ON
GO
INSERT INTO employee (emp_id, emp_name, country_code)
SELECT emp_id, emp_name, country_code from employee_temp(nolock) where emp_id > 110000000
GO
SET IDENTITY_INSERT employee OFF
GO

No comments: