How to insert VFP data into SQL Server?

[pctest]

Hi,

From what I understand, there are the following ways to import VFP data into SQL Server:

1. Convert VFP data into .dat and use bcp.
2. Convert VFP data into .dat and use BULK INSERT.
3. Transfer VFP data directly using DTS.

Which one is faster?


Thank you for any help.

 

[Mike Lewis]

Pctest,

bcp is just a wrapper for BULK INSERT, so there is not likely to be much difference in performance.

With DTS, the issue is what format you use for the import. If you can save the table as Fox2X format, you might be able to get DTS to handle it natively. That's likely to be faster than the other method, which is to use ODBC within DTS. On the other hand, it will take time to save it to Fox2X, so that extra time has to be taken into account.

For that matter, if you use bcp or BULK INSERT, you will also have to factor in the time needed to convert to a DAT (or other text file).

Have you considered running some tests to determine which is the best performance for your particular data?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[pctest]

Hi Mike,

The VFP data is VFP 5 data.

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

1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables of 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 (faster) 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 the data in DTS to replace step 1 and step 2

Thank you for any suggestion.

 

[SonicChen]

oh my god, u are discussing the problem i have encountered.
but what's DTS and bcp/BULK INSERT?

way to gold shines more than gold

 

[SonicChen]

i have google these concept just now.
but where to download bcp/bulk insert/dts?
thanks a lot

way to gold shines more than gold

 

[Mike Lewis]

SonuChen,

what's DTS and bcp/BULK INSERT

These are import tools, and are specific to SQL Server. I believe you are using Oracle. You will need to look for Oracle equivalents.

However, if the amount of data you wish to transfer is fairly small, you might find it easier to write the code yourself in VFP, as per my example in thread1252-818989.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Mike Lewis]

Pctest,

I'm still unsure about which approach will be the fastest, which is why I suggested that you do some timing tests. But you should also consider my suggestion of saving the table to Fox2x. It might possibly be the fastest solution.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[pctest]

Hi Mike,

My understanding is that you're suggesting to convert the VFP 5 tables to Fox 2x format first and then DTS. I don't understand why it is necessary. I believe DTS can use VFP 5 tables as the source. Am I wrong?


Thank you.

 

[Mike Lewis]

Pctest,

My understanding is that you're suggesting to convert the VFP 5 tables to Fox 2x format first and then DTS. I don't understand why it is necessary. I believe DTS can use VFP 5 tables as the source. Am I wrong?


This is purely a matter of performance.

You are right that DTS can read VFP 5.0 tables, but it cannot do so natively. You will need to set up an ODBC data source for the tables.

The alternative is to convert the tables to Fox2X, in which case DTS will read them natively.

Obviously, the first option is more convenient. But your original question was about performance, and my point was that the Fox2X option might be faster. On the other hand, by the time you have factored in the time needed to actually run the Fox2X conversion, the overall time saving (if any) will be much less.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[pctest]

Hi Mike,

You're definitely right that I'm concerning about performance. It is interesting to know that DTS can import fox2.x natively. Well, first I need to find out how to convert VFP data into fox2.x format. Second, I need to find out how to set up DTS to read Fox2x data. So far, I cannot find any info. Do you know whether I can find info on those 2 area?


Thank you for any help.

 

[Mike Lewis]

Pctest,

first I need to find out how to convert VFP data into fox2.x format.

That's easy:

SELECT MyTable
COPY TO MyNewTable TYPE FOX2X

Second, I need to find out how to set up DTS to read Fox2x data.

I don't think DTS mentions Foxpro or Fox2X by name, but if you look for a file type called dBASE or DBF, you should find it.

If, for any reason, that doesn't work, you can use a comma-delim file instead.



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com