Moving member information from on sql database to another sql database?

Question

I have a dating software that currently has members in the database. I have recently purchased another dating software and want to migrate my member information so as not to have anyone to re-register.

A friend told me that i could export the old database into access and then import it into the new one through the control panel. My concern is that the fields may not all match. If that is the case is there a way I can point them to where I need them to go?

I do have access on my pc so exporting it is not an issue. I just want to make sure I am on the right path.

Thanks in advance for your help

Answer 1

your concern is correct, you will have to find out if the columns are identical between the 2 dbs. if they are not, then you will have to manipulate the export before you can import to the new db or the import will most likely fail.

Answer 2

You don't necessarily need MS Access to do this. What you need to research is the field structure of the tables and look for a way to export/import the data. You could export the data to a text file using a delimeter (tabs, commas, or another unique character) or if going between different versions of the same database software, you may have an export/import utility. As you said, you do need to be careful about the fields. An import utility should have the ability for you to define field mappings (i.e. "field1" in the export file maps to "fieldA" in the new database, "field2" in the export file goes to "fieldC" in the new database, etc...). When you do the mappings though, realize that data integrity checks in the new database structure may require that you populate certain fields with a default value, even if it didn't exist in the old database (i.e. the new data structure includes a field for "optin" to represent special offers and must have a true or false value, but the old database had no data for this).

Answer 3

I would recommend using SSIS if you're using SQL Server 2005 or 2008 or SQL DTS for most earlier versions.



Tutorial for SSIS: http://msdn.microsoft.com/en-us/library/ms167031.aspx



DTS: http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/



Good Luck!