Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have to migrate existing data which is in SQL Server 2000 to SQL Server 2005. The schema of two databases is different. For Example Locations table in SS2000 is split into two tables and has different columns. This is one time activity. After successful migration I don't need old db anymore.

  • What is the best way to transfer data from one SQL Server to another having different schemas?

I can write stored procedures to fetch data from SQL Server 2000 and insert/update tables in SQL Server 2005.

  • What about SSIS? I don't have any experience with this and is this better to create package of SSIS because I don't need this again and need to learn it first.

Thanks.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

In any case you will have to write specific transformation logic since your schemas are different, even with SSIS. There is nothing that will be able to discern where to put things in the new schema just by comparing the two schemas.

SSIS has utilities to help you develop that transormation in a more visual style but if you are unfamiliar with SSIS then it can have a bit of a learning curve. My opinion is that you're better off writing stored procedures for just a one time migration.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.