One thing that DBAs often have difficulty with is moving data from one database to another database. This may be to synchronize a development environment with databases or tables on other servers with the same data values. Fortunately MSSQL provides a simple SQL solution.SET IDENTITY_INSERT that allows you to turn on and off the auto generation of the identity value . The command gets turned on (turns off normal autonumber) prior to the inserting of data and gets turned off after the data has been inserted.
// First we create a new record in our table
INSERT INTO dbo.tablename (FieldName) VALUES ('fieldvalue1')
// which creates record with ID 405 in our table.
// Then we copy that data to our duplicate database using the
//IDENTITY_INSERT attribute overriding the ID fields natural counter.
SET IDENTITY_INSERT dbo.nexttablename ON
INSERT INTO dbo.nexttablename (ID,FieldName) VALUES (405, 'fieldvalue1')
//OR
INSERT INTO dbo.nexttablename (ID, fieldvalue1)
SELECT * FROM dbo.tablename
SET IDENTITY_INSERT dbo.nexttablename OFF
