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.<\/p>\n
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 … Continue reading \/\/ First we create a new record in our table
\nINSERT INTO dbo.tablename (FieldName) VALUES ('fieldvalue1')
\n\/\/ which creates record with ID 405 in our table.
\n
\n\/\/ Then we copy that data to our duplicate database using the
\n\/\/IDENTITY_INSERT attribute overriding the ID fields natural counter.
\nSET IDENTITY_INSERT dbo.nexttablename ON
\n
\nINSERT INTO dbo.nexttablename (ID,FieldName) VALUES (405, 'fieldvalue1')
\n\/\/OR
\nINSERT INTO dbo.nexttablename (ID, fieldvalue1)
\nSELECT * FROM dbo.tablename
\n
\nSET IDENTITY_INSERT dbo.nexttablename OFF<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"