INFO: Transferring Tables and Data from Paradox to SQL Server (67809)

MORE INFORMATION

Method 1

The first method involves using the Paradox SQL Link to transfer the data. The steps to accomplish this are as follows:

1. Create tables in a SQL Server database that correlate to those you have defined in Paradox.
2. In Paradox, create replicas of the tables in SQL Server by playing the sqlsetup script. This will allow you to remotely access your SQL Server tables from within Paradox.
3. Select the Tools More Add option. At this point, enter the Paradox table you want to transfer as the source and the corresponding SQL Server table as the target.
4. Paradox will then transfer the rows into SQL Server. If Paradox is unable to transfer any of the rows to the SQL Server, it will place them in the Problems table.

This method should only be used with small tables. There are several reasons for this. First, Paradox moves the tables by individually inserting the rows into SQL Server. Because of this, the insertions are logged in the transaction log. For large amounts of data, this can cause the syslogs table in SQL Server to fill up and prevent additional insertions until the transaction log has been dropped. In addition, it can significantly increase the total amount of time required to move the data.

Method 2

The second method involves exporting the data to ASCII files and importing these files into the corresponding SQL Server tables using the BCP utility included with SQL Server. The steps to accomplish this are:

1. Create tables in a SQL Server database that correlate to those you have defined in Paradox.
2. Select the Tools Export/Import Export ASCII option in Paradox. At this point, enter the name of the table you want to export, and the name you want to be given to the converted file.
3. In SQL Server, set the SELECT INTO/BULKCOPY database option to true for the SQL Server database and issue a checkpoint in the database. In addition, remove all indexes from the tables you want to load the data into.
4. At this point, you will be able to use BCP to perform a nonlogged bulk copy of the data into your SQL Server tables.

This method is not only faster, but it will keep your transaction log from filling up. Additional information on transferring data with the BCP utility can be found in the "Transferring Data to and from SQL Server" section of the "Microsoft SQL Server Systems Administrator's Guide."