Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    54

    Unanswered: Insert Records from Foxpro tables to SQL Server tables

    Hi,

    Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:

    1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
    2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.

    I only know the following ways to import Foxpro data into SQL Server:

    #1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
    #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
    #3. DTS Foxpro records directly to SQL Server tables

    I'm thinking whether the following choices will be better than the current way:

    1st choice: Change step 1 to use #2 instead of #1
    2nd choice: Change step 1 to use #3 instead of #1
    3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2

    Thank you for any suggestion.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are more ways to skin a cat than there are cats, but that is no reason to stop trying!

    Without knowing a lot more about your situation, it is tough for me to reocmmend any one approach. A lot will depend on whether you are closer to wanting the data "as is" from your FoxPro tables, or "cleaned up" as you intend to use it going forward in SQL.

    There are also two options which you haven't mentioned. FoxPro can gleefully use SQL Server as its data store (instead of DBF files). SQL Server will happily read DBF files made visible using sp_addlinkedserver.

    While I can offer lots of opinions, you are the one that needs to make it work. If you have a preference for one method over another, then I'd say that you should go for it!

    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    54
    Hi Pat,

    I'm moving from a pure VFP application which read/write data to VFP tables to a VFP application which read/write data to SQL Server tables. So, I need to convert all the existing VFP data to the SQL Server tables so the new VFP application can read/write data. The VFP tables and the SQL Server tables are different in both structures and relationships.

    The current way to convert the existing VFP data to the SQL Server tables:
    1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
    2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables. For example, VFP table cust_vfp has name, address1 , address2, and other columns which is converted to 2 SQL Tables cust_sql which contains name and other columns and another one custaddr_sql contains name, address so each name can relate to multiple addresses.

    Thank you for any help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •