April 3, 2008 at 10:22 am
I am needing to map two databases to a new database and convert the data.
What is the best approach for this? I'm debating if I should use a tool (perhaps SSIS?) or just write out my scripts. I want to choose the best method for ensuring that all me relationships are maintained.
Thanks
cj
April 3, 2008 at 11:37 am
That's not really enough information to judge.
By "map two databases to a new database", do you mean you're transfering tables and data from two databases into one database?
If so, what is the purpose of this?
If, for example, the idea is that web pages and other applications should only attach to one database, instead of two, then maybe what you need to do is keep the two databases intact, and put together a separate database that consists of nothing but stored procedures that do all their work in the existing databases. That way, you keep all your existing data, relations, indexes, keys, constraints, etc., intact.
If what you need to accomplish is merging the data from two databases, with (for example) customer records in both, then SSIS is probably a good means of doing this, but it can probably be done with scripts just as easily.
It really depends on what the purpose of the action is, what the necessary end-result is, and what you current have.
Other questions to ask are: Does one database have nearly everything you need, and the other just has a few objects/tables/records to merge? Or is it a nearly even split between the two?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 12:34 pm
I am taking data from two different databases and integrating it into one. I had no input into any of the three databases. I have no say in the business decision logic to do this. I was essentially told "move all this data from Point A and Point B to Point C and make it fit."
I like SSIS but do not have time for a learning curve, especially for a one-time data conversion so if both works then I'll just write scripts and see what I can get done.
Thanks for the input.
April 3, 2008 at 1:37 pm
Do you have to merge data from the two into common tables?
For example, is there a customers table in each database and you have to make it into a single customers table in the third database?
If not, then using the Import/Export wizard in Management Studio might be an easy way to accomplish what you need. Right-click the third database, select Tasks, Import. Then follow the directions. It will allow you to move multiple tables around, and all that, in one fell swoop. What it won't do is define constraints, etc., on the tables.
If you want to keep the structure from one of the databases, you can script that database from Management Studio, make the necessary changes to the script to accommodate data from the second database, and then run that to create the tables, procs, etc. Once you've done that, the Import/Export wizard might be a good way to move data around.
There are "better" ways to do this, but this might be the simplest way.
(I wasn't challenging you on the need for the merge. I was just asking what the desired end result, and the reason for it, because that has a big impact on what to suggest you should do and how you should go about doing it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 3:19 pm
I wish it were simple enough to just move a couple of tables to an identical table but it is not. One field from Table1 will go to TableA and another field from Table1 will go to TableB and so forth. I might be able to do custom queries using the "Import" feature. I'll have to see.
Thanks for the input! 🙂
April 4, 2008 at 7:17 am
Sounds like a job for a set of complex, custom scripts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply