March 20, 2017 at 5:31 am
richlion2 - Monday, March 20, 2017 5:26 AMIt depends on how your application works, however whichever method you use as a general rule when importing tables after clearing it's best to disable all triggers on relevant tables, import and then re-enable triggers. It's pretty much a general approach that is true in the majority of import scenarios, especially if you have triggers where you don't know what they do. Triggers are designed by developers to serve a specific purpose within the application, updates are in specific order, etc. If you start a bulk import with triggers eanabled the import may completely go wild and destroy the well built-in consitency of your database, could also lead to corruption (meaning sensless data structure). Disabling Primary Keys is also advisable, as you may not know the exact order in which your tables will have to be imported. It's best to test such an export/import and develop the correct procedure.
There is a potential problem with disabling triggers, however. If the triggers do anything to tables which are not included in the 'refresh set', this all goes wrong, because the data in those tables will not get processed in the way it should.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 23, 2017 at 6:47 am
SQL!$@w$0ME - Wednesday, March 15, 2017 6:07 AMPerry Whittle - Tuesday, March 14, 2017 11:09 AMSQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello Folks,What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.Many thanks!
Use some code to do what has already been mentioned
- Drop the FKs
- Truncate the tables
- Set IDENTITY_INSERT on for the destination table, if you're not worried about the id values then skip this
- Load the tables from production using select queries, a linked server would work here
- Turn off the IDENTITY_INSERT if you switched it on
- Create the FK constraint
Hi Perry, Should I disable all triggers before data load using linked server or export import and enable after the load?
Generally, yes, you don't want triggers firing during the import but as mentioned it all depends.
If you're importing all tables and the trigger is firing to update a table you're also importing to it could cause duplicates
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 23, 2017 at 10:26 am
Perry Whittle - Thursday, March 23, 2017 6:47 AMSQL!$@w$0ME - Wednesday, March 15, 2017 6:07 AMPerry Whittle - Tuesday, March 14, 2017 11:09 AMSQL!$@w$0ME - Monday, March 13, 2017 4:19 PMHello Folks,What is the best approach to refresh UAT table from Production. Tables have foreign key constraints and identity keys. Tables on Prod and UAT have the same structure.
Please list the detailed steps for this task.Many thanks!
Use some code to do what has already been mentioned
- Drop the FKs
- Truncate the tables
- Set IDENTITY_INSERT on for the destination table, if you're not worried about the id values then skip this
- Load the tables from production using select queries, a linked server would work here
- Turn off the IDENTITY_INSERT if you switched it on
- Create the FK constraint
Hi Perry, Should I disable all triggers before data load using linked server or export import and enable after the load?
Generally, yes, you don't want triggers firing during the import but as mentioned it all depends.
If you're importing all tables and the trigger is firing to update a table you're also importing to it could cause duplicates
Thanks a lot
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply