Solved

moving 80 tables from one db to another server/db but with different where clauses

Posted on 2014-03-31
5
158 Views
Last Modified: 2014-04-10
2 queries as below

SELECT * FROM   PAGORI.RASA.MARKING_TARGET   WHERE  PICK_CODE_DATE IN ('2011-07-31','2012-01-11')
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','4377','8443','9843','4332')  
SELECT * FROM   PAGORI.RASA.PICTOUR  where   OFFICE_CODE IN ('3262','4373','4373','4377','8443','9843','4332')
4 other such table transfers

--------------------------
 
SELECT * FROM   PAGORI.RASA.RESPROT WHERE OFFICE_CODE IN ('3262','4373','4373','4377','8443','9843','4332') AND PICK_CODE_DATE IN ('2011-07-31','2012-01-11')
SELECT * FROM   PAGORI.RASA.SCOREMNT WHERE OFFICE_CODE IN ('3262','4373','4373','4377','8443','9843','4332') AND PICK_CODE_DATE IN ('2011-07-31','2012-01-11')
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
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39967792
Hi,

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
0
 
LVL 5

Author Comment

by:25112
ID: 39968238
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.
0
 
LVL 5

Author Comment

by:25112
ID: 39968257
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.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39970391
Hi,

This does not look something that can be easily data driven, unless those conditions are the same for each and every table.

If that is the case, create a table that has the tablenames in it, and write a cursor or while to loop through the 80 tables and do them one-by-one with some dynamic code.

And if you need another 100 tables done in the future, then that is realtively easy.

HTH
  David

PS Dropping a table has minimal logging (and locking), and if initially the restored database is in simple mode while dropping the unwanted tables and deleting unwanted rows then the logging shouldn't be too bad. Then change recovery model to full and take a backup.
0
 
LVL 5

Author Comment

by:25112
ID: 39992461
thank you David-
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question