Petros,

BCP, BULK INSERT or using the BULK INSERT cpability of SQL Server are the fastest ways to get any Data into SQL. All other methods first send data to SQL which first writes to SQL Transaction Log File then SQL writes to Table. The above bypass this. BULK INSERT is the fastest of the three according to my studies.

If you are looking for raw speed then use one of the three. However to use BCP or BULK INSERT with the least amount of problems your incoming data has to be clean and the field orders must match the SQL field orders.

I am migrating a VFP 3.0 -> VFP tables app to VFP 7.0 and SQL Server 2000. I am at the moment writing the app that moves all of the data from VFP -> SQL Server. 75 tables, 4.5 gig of fox data, 15 million records. I worked with the above but I needed some flexibilty at the point of moving a table into SQL.

The only way to get any flexibility out of BCP or BULK INSERT is to use a Format file. (DTS - BULK INSERT uses the same basic part of SQL to do the insert but you cannot use a format file.) I had many problems with the format file of which I posted questions in this VFP forum and the SQL Forum of UT. I did not get a single response. I did get a response on another forum but still could not find answer to problem so....


What I found was that running the VFP Migration app on the same box as SQL Server with the VFP data being on the same box screams on VFP/SQL Pass through. I am doing a SCAN/ENDSCAN then a SPT INSERT command. The advantage to this is that if the individual record errors on insert I have a corresponding log record (my own) that makes it easy to deal with after the fact and only that individual record does not go into SQL.

If BCP or Bulk insert coughs on the last record the whole import is abandoned and the returned error leaves something to be desired.

If I had one file and one import then I would have worked through my problmes with BULK INSERT.
If I had one file and was going to repeatdly bring in that file (new update to file a week) then I would use BULK INSERT and work through problems.

Several files I would consider DTS.

Many files I would look to VFP with SPT.

Ron




>Hello all,
>
>Questions
>=========
>
>1. How can I connect and call SQL server store procedure from VFP ?
>
>2. I tried to insert records 1000000 in a #temp table in sql server and it was very slow (7 minutes). In VFP is 7 seconds !!. There is any way to make SQL server faster? (The server is 6 processors, 2GB ram, RAID5).
>
>3. I continued my tests with SQL statements and VFP is still faster. The problem is that SQL server does not have the concept of VFP cursor ?
>
>4. There is a document of explaining this behaviour (SQL Server / VFP) ?
>
>Thanks in advance
>Petros