Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have several tables setup with IDENTITY INSERT OFF and I need to change this to IDENTITY INSERT ON. A lot of these tables have 500,000+ records. These tables are basically mirrors of other tables and this is a live system. Can I change this without causing to much havoc?

Example: I have a StagingProducts table that users can change their product data in and then when they are done the push these changes over to the Products table which is basically my live table that end users query.

IDENTITY INSERT is OFF on this Products table so the ProductID which is the IDENTITY Column is getting huge and it differs from the StagingProducts tables. I can simplify some of my code greatly if this ProductID is the same across both tables and improve performance.

Is it safe to do this?

share|improve this question
2  
Not totally clear what you are doing/asking. Some example code /table structures / data might help. Just to be clear IDENTITY_INSERT is a session option that can be on for one table at a time and allows you to insert explicit values not a property of the tables themselves. – Martin Smith Mar 14 '12 at 18:56
3  
Why are the tables' ProductIDs different? You seem to have a data quality problem. IDENTITY_INSERT will not help you unless the ProductIDs in staging are not already in production and you just want to insert them into prod; however, if you have duplicate products with different IDs, then you have a data quality issue and you need to resolve that first. – ooutwire Mar 14 '12 at 20:56
I think an important question is, why are the users modifying data in a staging table, just for it to get migrated to a "live" table for querying? – DForck42 Mar 16 '12 at 18:28
@DForck42 The 'users' modifying this data are administrators and they may need a day or a few days to tweak their data before making it live to their customers so we have a staging table that let's them modify their data until it is ready. – Slee Mar 19 '12 at 11:25
@ooutwire I was planing on dumping all of the live data form those tables and do a single push of everything from staging to live once so they end up being the same. I do not have any duplicate products with different ID's – Slee Mar 19 '12 at 11:27
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.