December 9, 2008 at 9:14 pm
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?
December 10, 2008 at 7:01 am
I don't know that you would be able to do this using the tools you mention. A work around would be to do the copy to tables in the dbo schema, then in the next step in the schedule drop the existing tables in the new schema and run ALTER SCHEMA transferring each table to the new schema.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 10, 2008 at 8:30 am
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!
December 10, 2008 at 8:38 am
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.
December 10, 2008 at 10:22 am
There is a schema mapping function in Compare that will do what the author was wanting (and found).
December 10, 2008 at 12:59 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply