I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

August Edition
Subscribe now! It's Free!
Enter your email:

   ► KBRole-Based T...DBA & Data   Print This    All Groups  
  From the January 2016 Issue of Prestwood eMag
 
DBA & Data:
Data Migration, Paradox to SQL Server
 
Posted 13 years ago on 4/26/2006 and updated 5/7/2010
Take Away:

some tips for data migration, especially with bitfield, or "logical" field types.

KB100362

I have yet to find a tool that's really useful as a "catch-all" utility that will move data from one database to another; and since you really NEED to understand your data model in order to program in the data-entry and query features anyway, you need to dig in regardless to lay out your data.

So, my approach for data migration is rather hands on.  I start with the tables of my paradox data model, and move the data over to SQL Server, one table at a time.

At this point, I "loosen" the data constraints.  I find it far more productive to simply "get the data over" to the new system, and THEN start to implement the "lockdowns" that make sure the data is good.

For instance, I've run into a LOT of situations where a LOGICAL field was used in Paradox, but the authors/maintainers have taken advantage of the fact that such fields are not binary, but rather trinary in nature.  They can be set to true, false, or blank.  True, the "NOT NULL" (in SQL parlance) setting ("Required Field" in Paradox) can be set to get around this; but far too many Paradox folks never went to the trouble.

When trying to port that type of information into a BIT type field in SQL Server, with the "Allow Nulls" setting to false (which would help guarantee data integrity), you will most likely run into a terrific amount of trouble, as Delphi throws an exception each time you try to stuff a "null" value into the bit field.

It's MUCH easier to either declare the field type nvarchar(1), and switch it later, or declare it bit / allow nulls, and then fix the allow-nulls setting later.

The sequence of events is: create the fields in SQL Server in a new blank table with the same layout as the source table; loosening restrictions as you go (allow nulls where you later won't want them, don't set a primary key on index fields that didn't have one before, even if they need one going forward, and so on).  Write the simple delphi app that allows you to transfer from a TTable object to a TADOTable object; run the app and move the data.  Repeat for all tables.

Then the next step is to use the SQL Server tools to "fill in the gaps" and "tighten ship".  Add in the data constraints as you go, using insert and set queries to fill in data to avoid problems as you step through the fields to be fixed.  Add primary keys, and then use the visual designer to set referential integrity and foreign keys.

Finally, rewrite the code to access your shiny new improved database!

More Info

FAQ:  BDE SQL Links Not Maintained
FAQ:  Char vs NChar

Linked Message Board Threads

 Writing from Paradox to SQL Server 2005 in Paradox to/from Other Data Sources MB Topic (3 replies)
 Data conversion from paradox to sql server in Paradox to/from Other Data Sources MB Topic (0 replies)
 Paradox 9 to SQL Server in Paradox to/from Other Data Sources MB Topic (0 replies)
 HowTo: Configure BDE to connect to SQL Server 2000 in Paradox to/from Other Data Sources MB Topic (0 replies)

Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1236A1
Enter key:
KB Post Contributed By Joshua Delahunty:
I am a software engineer with many years professional experience, using everything from 6809 assembly language to C, C++, Delphi, Paradox for Windows, and Borland JBuilder.
Visit Profile

 KB Article #100362 Counter
14515
Since 4/2/2008
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech