Disable Indentity Field

[dokken]

I have an ASP application that uses a SQL server database. I have made all PK fields Identity fields so the DB an take care of assigning the values. I also would like to be able to run an ASP script that copies our production data to our test database. I would like to retain the PK values. How can I easily temporarily turn off the identity fields so I can write to the PK field?

Thanks
Paul

 

[grahamm]

1)You can enable inserts to an identity column via set. i.e.
SET IDENTITY_INSERT [database.[owner.]]{table} ON

The limitation is a session can only have one table with identity_insert activated at any one time. So your script will have to bracket insert stmts with SET ON and OFF commands.
OR
2) If the production data is in files use BULK INSERT with the KEEPIDENTITY option. See the BOL

 

[RickCole]

As a possible alternative you could always backup the production database and restore over the top of your test database.

Rick.