My company has been using R:BASE in the past and we recently upgraded our network and installed SQL. How can I migrate my old R:BASE data into SQL?
I could really use your help because to reenter all of the records would be entirely too labor- and time-consuming.
Ah… I remember R:BASE. 🙂
Export the R:BASE data into text files. I think SDF (is that the one with fixed-length fields and no delimiters?) is the way to go if you have a choice. Make one text file for each table in R:BASE and save it to the BIN directory under your SQL installation. (I’m thinking SQL 6.5 here, but the idea still works in SQL 7.0.)
On the server, create mirror image tables of the stuff you are importing from the text files. Think of them as staging tables.
Let’s say your R:BASE source has a column with a date of birth in it and it was in a date datatype. Chances are that getting the R:BASE date datatype into the server whole will be a problem because of the differing expectations of the products over what a date datatype looks like. The answer is to export from R:BASE as character, and import it into SQL Server as SQLCharacter.
Now that you have it in the server, create a new set of tables. In the table definitions, create columns with the appropriate datatypes. Now you can load your production schema from the SQL Character-imported data by using the convert and datepart functions to coerce the data back into its proper datatype.
DTS does this a whole lot easier in SQL 7.0.