The Best Way For Transfer Data From One DataBase (sql server 2008) To Another db(sql server 2008) With Different Schema....

Question

hi my dear friends :

what is the best way For Transfer Data From One DataBase (sql server 2008) To Another db(sql server 2008) With Different Schema?

is there a program for doing that ?

thanks 4 your future asnwer

best regards

Answer 1

Depending on how complex your needs are, you may want to look into using SSIS.

Answer 2

I would use SSIS (which comes with sql server) to do this. This will not be an easy or quick task however. Mapping from one schema to another can be very time consuming and you will almost certainly find data that doesn't fit from one schema to the other that you have to deal with. For example if the field is varchar in the first schema and datetime in the second, you will almost certainly have to exclude bad values (like "ASAP") which can be a problem especially if the field is a required field. In fact you will have to deal with those types of mismathces as well - if it wasn't required and now is, what value do you put in there for the records where it currently is null. What do you do when field sizes don't match up, you may have to truncate data. If you have different lookup values from one to the other, you need a conversion table. You may need a conversion table to relate old and new id fields so that you can properly load child tables. This is a very complex task.