Migrating data from SQL Server 2000 to SQL Server 2005

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 wr开发者_C百科ite 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.

EDIT:This edit is after answer from @AgentDBA:

One example of data in Old db and how I need this in new db is:

Customers table in Old DB has column FullName like 'James White' but in new db there are two columns FirstName and LastName so data in this should be 'James' in FirstName and 'White' in LastName.

Thanks.

Although it would be an excellent exercise to use this as an opportunity to learn SSIS, I imagine real world deadline pressures will dictate that you're better off sticking with the tools you know (T-SQL, stored procs) rather than trying to learn something brand new, especially for a single use transformation like this.

You should be able to use the Data Import Wizard from SQL 2005 to import the data for you.

Right click on the database choose Tasks, Import Data this will lead you through a nice wizard that will ask where you are getting the data from and where the data will end up.

Admitedly you would need to do this for each table you are importing the data to in the new SQL2005 database so it can be long winded. However if you save the package (which it should ask you near the end of the wizard) to the desktop then it will save as an SSIS package which is a good way to start to learn SSIS.

Plus as the package is saved you can run it again should you need to.

If the two databases are on different servers, SSIS will handle the cross server movement easily while in T-SQL you will have to have linked servers set up. That isn't a big deal as long as you are aware that you need to do it, but some dbas don't allow them on their systems, so if you can't set up a linked server for political reasons, SSIS is the better choice. Otherwise, I'd go with what you know since it is a one-time only thing unless you will be doing lots of other data imports as well in the future (do you currently have lots of DTS packages?). In this case, might as well get started learning SSIS now as it isn't so very simple to learn and there is typically more time to learn before the new db goes live thatn after when the clients are cranky becasue their data isn't in and they sent you the file a week ago.