>I have an application written in VFP 6.0 that is being rewritten in C# with SQL Server backend.
>
>I will need to migrate the VFP data to the SQL Server tables. I haven't written any VFP code in ~10 years so would like to inquire as to the best mechanisms to migrate the data.
>
>I am figuring the most straightforward way will be some VFP prgs with custom code iterating the VFP tables. If so, can anyone provide the basic commands and syntax to use?
>
>Thanks,
>Bill

Here's some code that I wrote a long time ago and have used dozens of times.
It uses a table called convdata.dbf for names that vary.
A nice feature is that you can direct it to create the SQL table if it doesn't already exist, or it will use an existing table.
By convention, our identity columns are named idcol, so there's special logic for them.
I wrote this because the upsizing wizards really sucked, and they required the tables to be in a DBC and most of mine were free tables.
I've heard that the SEDNA version is better.

Here's the code.

CLOSE ALL
CLEAR ALL
SET SAFETY OFF
SET ESCAPE ON
SELECT 0
USE convdata

*-- SQL Connection name
lc_user = "CONV"

ll_cont = .T.


*-- ODBC Connection
gc_sqlconnect = ALLTRIM(convdata->sqlconn)
gc_sqluser = ALLTRIM(convdata->sqluser)
gc_sqlpassword = ALLTRIM(convdata->sqlpw)


*-- Source Table
lc_source = ALLTRIM(convdata->source)
*-- Target SQL Table
lc_target = ALLTRIM(convdata->target)
*-- Target Database
gc_database = ALLTRIM(convdata->sqldb)
*-- Create indicator
ll_createtarget = convdata->createtab
*-- ZAP indicator
ll_zaptarget =  convdata->zaptarg
*--
ll_displaytarget  = convdata->disptarg
USE IN convdata

SELECT 0
USE &lc_source ALIAS a_source



*-- Connect to SQL Server
SET MESSAGE TO "connecting"
gnsqlHandle = SQLCONNECT(gc_sqlconnect, gc_sqluser, gc_sqlpassword)

= SQLSETPROP(gnsqlHandle, 'Transactions', 2)  && Manual transactions
= SQLSETPROP(gnsqlHandle, 'asynchronous', .F.)
= SQLPREPARE(gnsqlHandle, "USE " + gc_database)
ln_exec = SQLEXEC(gnsqlHandle)

IF ll_createtarget

  *-- Drop the target table
  = SQLPREPARE(gnsqlHandle, "DROP TABLE " + lc_target)
  ln_exec = SQLEXEC(gnsqlHandle)
  ln_commit = SQLCOMMIT(gnsqlHandle)  && Commit the changes

  ld_datetime = DATETIME()
  lc_time = TIME()

  ll_cont = .T.

  SELECT a_source
  = AFIELDS(la_fields)

  *-- Create the table if needed

  lc_createcomm = "CREATE TABLE " + ALLTRIM(lc_target) +  "("
  FOR x = 1 TO ALEN(la_fields,1)
    *-- Add the commm separator if necessary
    IF x <> 1
      lc_createcomm = lc_createcomm + ","
    ENDIF
    *-- Field name
    lc_createcomm = lc_createcomm + " " + LOWER(ALLTRIM(la_fields(x,1))) + " "
    *-- Type
    lc_type = la_fields(x,2)
    DO CASE
    CASE lc_type = "D"
      * Datetime
      lc_createcomm = lc_createcomm + " DATETIME "
    CASE lc_type = "N"
      * Numeric
      lc_createcomm = lc_createcomm + " DECIMAL (" + ALLTRIM(STR(la_fields(x,3))) + "," + ALLTRIM(STR(la_fields(x,4))) + ")"
    CASE lc_type = "B"
      * Double
      lc_createcomm = lc_createcomm + " FLOAT "
    CASE lc_type = "Y"
      * Currency
      lc_createcomm = lc_createcomm + " MONEY "
    CASE lc_type = "L"
      * Logical
      lc_createcomm = lc_createcomm + " BIT "
    CASE lc_type = "M"
      * Memo
      lc_createcomm = lc_createcomm + " TEXT "
    CASE lc_type = "I"
      * Integer
      lc_createcomm = lc_createcomm + " INT "
    OTHERWISE
      * Character
      lc_createcomm = lc_createcomm + " CHAR (" + ALLTRIM(STR(la_fields(x,3)))+ ")"
    ENDCASE
    lc_createcomm = lc_createcomm + " " + " NULL" + CHR(13)
  ENDFOR
  lc_createcomm = lc_createcomm +  ")"
  = SQLPREPARE(gnsqlHandle, lc_createcomm)
  ll_cont = ln_execsql(gnsqlHandle)
  IF ll_cont
    ln_commit = SQLCOMMIT(gnsqlHandle)  && Commit the changes
  ENDIF
ENDIF  ll_createtarget

IF ll_cont
  *-- ZAP target if necessary
  IF ll_zaptarget
    *-- Zap the target table
    = SQLPREPARE(gnsqlHandle, "DELETE FROM " + lc_target)
    ll_cont = ln_execsql(gnsqlHandle)
  ENDIF
ENDIF ll_cont


IF ll_cont

  *-- Create the insertion command
  lc_insertcommand = " INSERT INTO " + lc_target
  lc_tostring = "("
  lc_fromstring = " VALUES ("
  SELECT a_source
  = AFIELDS(la_fields)
  FOR x = 1 TO ALEN(la_fields,1)
    IF UPPER(ALLTRIM(la_fields(x,1))) <> "ID_COL"
      lc_tostring = lc_tostring +  IIF(x =1, "", ", ") + LOWER(la_fields(x,1))
      lc_fromstring = lc_fromstring + IIF(x =1, "", ", ") + " ?m." + la_fields(x,1)
    ENDIF
  ENDFOR
  lc_tostring = lc_tostring + ")"
  lc_fromstring = lc_fromstring + ")"
  lc_insertcommand = lc_insertcommand + lc_tostring + lc_fromstring

  SELECT a_source
  GO TOP
  SCAN WHILE NOT EOF()
    SELECT a_source
    SCATTER MEMVAR MEMO
    = SQLPREPARE(gnsqlHandle, lc_insertcommand)
    ll_cont = ln_execsql(gnsqlHandle)
    IF NOT ll_cont
      EXIT
    ENDIF
  ENDSCAN
ENDIF ll_cont

IF ll_cont
  ln_commit = SQLCOMMIT(gnsqlHandle)  && Commit the changes
  WAIT WINDOW "Conversion Complete"
  IF ll_displaytarget
    = SQLPREPARE(gnsqlHandle, "SELECT * FROM " + lc_target, "curtarget")
    ln_exec = SQLEXEC(gnsqlHandle)
    SELECT curtarget
    GO TOP
    BROW
 endif
ENDIF ll_cont
 = SQLDISCONNECT(gnsqlHandle)

FUNCTION ln_execsql
*-- Execute SQL Command and display errors, if any
PARAMETERS pc_ConnHandle
PRIVATE ll_return, ln_exec,la_error
ll_return = .T.
ln_exec = SQLEXEC(pc_ConnHandle)
IF ln_exec < 1
  ll_return = .F.
  WAIT WINDOW "SQL ERROR Has Occurred "
  = AERROR(la_error)
  WAIT WINDOW la_error(1,2)
ENDIF
RETURN ll_return