Migrating data from SQL 2000 EXPRESS to SQL 2005

Question

Migrating data from SQL 2000 EXPRESS to SQL 2005

olie480

SSC-Addicted

Points: 443
More actions
February 8, 2010 at 10:44 am

#215394

Hello all!
I have been a subscriber to SQL Server Central for about 2 years now, and I get a lot of great tips from the site, and I appreciate everyones helpful advice to shed light on some of the most grueling tasks.
I am not all that new to SQL, but consider myself to be a newbie.
The problem that I am facing is, I have a POS program (Comcash) using sql 2000, with a lot of data that needs to be mapped into a NEW POS Program (Microsoft Dynamics RMS) We don't need financial summaries, but will just need the basics (customer info, products, vendors, etc.)
Does anyone have any good tools, or tips they could recommend to me to easily map all the data from database 1 into database 2?
One of the problems I also may run into is that the data types of some of the fields, (i.e CompanyName datatype on database 1 is varchar(40) while CompanyName datatype on database 2 is nvarchar(50) )
Any help, or light shed on this subject would be great, and I look forward to your responses.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

One of the problems I also may run into is that the data types of some of the fields, (i.e CompanyName datatype on database 1 is varchar(40) while CompanyName datatype on database 2 is nvarchar(50) )

You should be able to backup and restore with 90 compatibility, yes there is difference between Varchar(40) and Nvarchar(50) which in Varchar is actually 100. The only difference is extra space hard drives are cheap. If you are going to 2008 the difference is more because Nvarchar in 2008 is unsigned integer while in 2000 to 2005 is in bytes. Post again if you still need help.

Kind regards,
Gift Peddie

olie480 SSC-Addicted Points: 443 More actions · Answer 2

I know I wouldn't have a problem with backup and restore if the database (or program) was the same, but in this instance, I have table names that are changing (which is not a big deal) but also column name changes, db1 = Name while db2 = FirstName, db1 = Email & db2 = EmailAddress, etc.

I just need something that would be simple to map the column names, to the other column names so that I can import the data to them. Just didn't know what good programs exist for that.

Thanks for clearing up the datatypes too!

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

olie480 (2/8/2010)
I know I wouldn't have a problem with backup and restore if the database (or program) was the same, but in this instance, I have table names that are changing (which is not a big deal) but also column name changes, db1 = Name while db2 = FirstName, db1 = Email & db2 = EmailAddress, etc.
I just need something that would be simple to map the column names, to the other column names so that I can import the data to them. Just didn't know what good programs exist for that.
Thanks for clearing up the datatypes too!

Did you try DTS import/export wizard?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

If columns are changing then you may need to use SSIS to move the 2000 to 2005, there is no SSIS in Express but you can either buy the developer edition which is under $50 or use the trial it is good for 180 days. When in 2005 just backup and restore to 2005 Express.

Kind regards,
Gift Peddie

olie480 SSC-Addicted Points: 443 More actions · Answer 5

Did you try DTS import/export wizard?

Wow! That's it! That is exactly what I was looking for! Thank you so much!

The new Microsoft POS (Dynamics POS 2009) has a tool that is pretty much the same thing, but it doesn't have all the features we wanted, so thats why we bought the older version that is pretty much manual when it comes to SQL.

Thanks again guys for all your help!

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

olie480 (2/8/2010)
Did you try DTS import/export wizard?
Wow! That's it! That is exactly what I was looking for! Thank you so much!
The new Microsoft POS (Dynamics POS 2009) has a tool that is pretty much the same thing, but it doesn't have all the features we wanted, so thats why we bought the older version that is pretty much manual when it comes to SQL.
Thanks again guys for all your help!

I did not recommend the DTS wizard because if the new changes are more complex you will still need to use SSIS.

Kind regards,
Gift Peddie