25112
asked on
moving 80 tables from one db to another server/db but with different where clauses
2 queries as below
SELECT * FROM PAGORI.RASA.MARKING_TARGET
SELECT * FROM PAGORI.RASA.FUND_SOURCE WHERE PICK_CODE_DATE IN ('2011-07-31','2012-01-11'
--------------------------
SELECT * FROM PAGORI.RASA.SOURCE WHERE OFFICE_CODE IN ('3262','4373','4373','437
SELECT * FROM PAGORI.RASA.PICTOUR where OFFICE_CODE IN ('3262','4373','4373','437
4 other such table transfers
--------------------------
SELECT * FROM PAGORI.RASA.RESPROT WHERE OFFICE_CODE IN ('3262','4373','4373','437
SELECT * FROM PAGORI.RASA.SCOREMNT WHERE OFFICE_CODE IN ('3262','4373','4373','437
70 more such table transfers
These 80 table data need to be transfered to another server (same schema on destination database). The 80 tables needs to be truncated and then loaded.
is the attached ssis the best method to do it..?
Untitled.png
SELECT * FROM PAGORI.RASA.MARKING_TARGET
SELECT * FROM PAGORI.RASA.FUND_SOURCE WHERE PICK_CODE_DATE IN ('2011-07-31','2012-01-11'
--------------------------
SELECT * FROM PAGORI.RASA.SOURCE WHERE OFFICE_CODE IN ('3262','4373','4373','437
SELECT * FROM PAGORI.RASA.PICTOUR where OFFICE_CODE IN ('3262','4373','4373','437
4 other such table transfers
--------------------------
SELECT * FROM PAGORI.RASA.RESPROT WHERE OFFICE_CODE IN ('3262','4373','4373','437
SELECT * FROM PAGORI.RASA.SCOREMNT WHERE OFFICE_CODE IN ('3262','4373','4373','437
70 more such table transfers
These 80 table data need to be transfered to another server (same schema on destination database). The 80 tables needs to be truncated and then loaded.
is the attached ssis the best method to do it..?
Untitled.png
ASKER
there are 500+ tables, David and some ofthem are too big.. so deletes will take lot of log maintenance and time..
i am open to ideas. thanks.
i am open to ideas. thanks.
ASKER
SSIS option seems like i have to hard code 80 queries? seems tedious, but benefit is i can save it and rerun as needed.. but if i need to do same concept for another set of 100 tables, that becomes hard again.
any way to automate this better by segments? 2 table transfers have one set of WHERE; 8 have another set of WHERE; and 70+ have another set of WHEREs.
any way to automate this better by segments? 2 table transfers have one set of WHERE; 8 have another set of WHERE; and 70+ have another set of WHEREs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you David-
Depending on what the source database looks like, would it be easier to restore a backup of the current database on the destination server, and then filter off what isn't needed?
HTH
David