|
|
|
SSC Journeyman
 
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 8:49 PM
Points: 86,
Visits: 106
|
|
I have a production ERP system and I want to get about 50 tables from it copied over to another database for reporting. I cannot use replication because the vendor isn't comfortable with replication and hasn't tested their database with it so they are against me using it.
As an alternative, I plan to use Red Gate's SQL Compare and SQL Data Compare Pro which will allow me to schedule command line execution of schema and data synchronization projects that I configure ahead of time. That will work just fine.
All of the tables in the ERP systems database are part of the dbo schema. However I want them to fall under a different schema in the reporting database.
How can I accomplish this?
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 6,301,
Visits: 5,577
|
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 8:49 PM
Points: 86,
Visits: 106
|
|
No it turns out you can do it and it works great.
I tried to delete this post an hour after I created it and before anyone could reply because I figured it out, but, there is a bug that prevents me from deleting any posts.
Thanks for your response though!
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 11:41 AM
Points: 19,451,
Visits: 5,034
|
|
We don't allow deletions because it would start to cause issues.
If you figure it out, it's best to post the answer yourself so others can see it.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, June 22, 2009 1:43 PM
Points: 246,
Visits: 284
|
|
| There is a schema mapping function in Compare that will do what the author was wanting (and found).
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 8:49 PM
Points: 86,
Visits: 106
|
|
Fair enough Steve, that makes sense.
Yes the answer is that SQL Compare and SQL Data Compare do have a feature that allows you to "Map Owner". It really should be called "Map Schema" now that SQL 2005+ has switched away from the concept of owners and to the concept of schema's but that's not important.
It should be noted that this is not a replacement for Replication, but a work around due to complications with our ERP vendor. Replication is much more efficient because it can store the transactional changes to a database (or subset of tables on a database) and push those out to subscribers or allow subscribers to pull them on a configurable interval.
Red Gate's compare tools have to do a full scan of all of the data and thus are slower than replication (although much easier to use). Fortunately, the few tables (less than 50) and the amount of data in this case allows me to get away with this for now.
Thanks to all who replied.
|
|
|
|
