Since I posted code below to create and execute VFP code to copy each table in a VFP database to text files that could be easily bulk inserted into SQL, thought I'd add the script on the SQL side that imports them. The tables structures can easily be created using Sedna Upsizer and checking no data.

Because bulk inserts are so much faster on very large tables ( the upsizer was choking badly for me on millions of records) and to handle my issue with many GUIDs that would become uniqueidentfiers in SQL, I just decided it was easier to create the tables with Sedna (which sees the GUIDS as char(36) and do the export/import in code I could control all the way. (and also take care of memo fields - I will add the code that exports and imports memos > 240 character to this thread later) I change the char(36) to UID, set the pks and add timestamps with scripts on the SQL side, generated by a script that writes a script - much as the example below.

Here's the script that writes the the script to import all the tables from txt files

-- Write script to bulk upload to every table

DECLARE @loadpath VARCHAR(200) = 'c:\users\public\sql\'
DECLARE @sql  VARCHAR(MAX) = 'Use Inmatetrustfund' + CHAR(13) + CHAR(10)
 

SELECT  @sql  = @sql+ 'BULK INSERT '+OBJECT_NAME(object_id)+ CHAR(13) + CHAR(10)+
' FROM '''+@loadpath+'C_' + OBJECT_NAME(object_id) + '.txt'''+  CHAR(13) + CHAR(10)+
' WITH (FIELDTERMINATOR = ''|'', ' + CHAR(13) + CHAR(10)+
 'ROWTERMINATOR = ''\r\n'')' + CHAR(13) + CHAR(10)+'GO' + CHAR(13) + CHAR(10)
FROM    sys.tables
ORDER BY OBJECT_NAME(object_ID)

-- of course you could just run the @sql script right here
-- I'm chossing to paste the output into its own script so I can conveniently reimport 
-- any of the 150 tables if something needs to be tweaked or the txt file needs to be recreated
-- that could also be done by creating an SP that would receive the name of a table, drop constraints, delete data,
-- import new data from txt file and reapply constraints

SELECT [processing-instruction(x)]=@sql FOR XML PATH(''),TYPE   
RETURN

Hope this saves somebody some time.

>>>>COPY TO 'PipeDelimitedFile.txt' DELIMITED WITH "" WITH CHARACTER '|'
>>>
>>>
>>>Turned out to be a very good way to go. Table has 2.5 million records. 6 columns had to be transformed from GUID(16) to GUID(32) . First did a SELECT to do the transform then the COPY to to create the text file. That took about 10 seconds all together. Bulk upload on the SQL side took about another 10 seconds.
>>
>>Hi,
>>How do you handle Memo fields ?
>>Copy to ignores them.
>
>
>I build a SELECT statement for each table and use the resulting cursor to feed the copy to. For memo fields, if the contents are < 240 I take them as part of the cursor. If they are greater, I put a tag in the cursor that sql can use later.
>
>I write out the contents of any memo field holding more than 240 characters to a txt file named tablename-pk-tag. TXT and then read those into the SQL table varchar(max) columns wherever the tag is encountered, figuring the name of the text file from the name of the table and column As long as the tag matches the txt file name, I'm good.
>
>Here's the code that builds the SELECT
>
>

>lcdefa=Sys(5)+Sys(2003)
>
>*this table is just used to store the SQL for each table for rerunning or debugging
>*the text file is created directly from the memvar
>
>USE tablesql IN 0
>SELECT tablesql
>DELETE ALL
>
>lcexppath = 'c:\users\public\sql\'
>lctxtpath = 'c:\users\public\memos\'
>
>lcpath=Addbs(This.oparentbizobj.getfield("cpath_to_vfp"))+"convert"
>Set Default To (lcpath)
>
>* the lf below was originally to build a multiline select which looks nice and is good to paste in command window but chokes macro expansion
>
>*lf = ";"+ CHR(13)+CHR(10)
>
>lf = ' ' 
>
>lcsql = "SELECT "+ lf
>
>
>********************************************************************************
>*  Prep INMATETRUSTFUND.DBC
>*********************************************************************************
>
>
>
>lcdbc="inmatetrustfund.dbc"
>
>* ITERATE TABLES
>
>Open Database (lcdbc) Exclusive
>
>ntables= Adbobjects(latables,"TABLE")
>
>For i=1 To Alen(latables,1)
>
>	lcdbf=latables(i)
>
>	lcalias=Juststem(lcdbf)
>
>	SELECT tablesql
>	APPEND BLANK
>	REPLACE tablename WITH lcalias
>
>	Wait Window lcalias Nowait
>
>	Use (lcdbf) In 0 Exclu
>
>	Select (lcalias)
>
>	=Afields(laflds)
>
>
>* SCAN FIELDS
>lnflds = ALEN(laflds,1)
>mcnt = 1
>	For N=1 To lnflds
>
>		lcfld=Alltrim(laflds(N,1))
>		lctype=laflds(N,2)
>		lnsize=laflds(N,3)
>		llnct=laflds(N,6)
>	
>		Wait Window lcalias + Space(10)+lcfld Nowait
>
>		Select (lcalias)
>
>		Do Case
>
>		Case Inlist(lctype,"D","T")
>
>			lcsql = lcsql + " TTOC("+lcfld+",3) as "+lcfld + IIF(N < lnflds,',','')+lf
>			* the IIF is so there will be no comma after the last column in the select
>	
>		Case (lctype="C" )
>
>			lcsql = lcsql + " "+lcfld+ IIF(N < lnflds,',','')+ lf
>			
>		Case Inlist(lctype,'I','N','Y')
>
>			
>			lcsql = lcsql + " "+lcfld+IIF(N < lnflds,',','')+lf
>			
>		CASE (lctype = "L")
>		
>			lcsql = lcsql + " IIF("+lcfld+"=.t.,1,0) as "+lcfld+IIF(N < lnflds,',','')+lf
>
>	
>		CASE (lctype = "M")
>		
>			lcsql = lcsql + " IIF(LEN(ALLTRIM("+lcfld+")) < 240, "+lcfld+",'MCOL+"+ALLTRIM(STR(mcnt))+"') as "+lcfld+ IIF(N < lnflds,',','')+lf
>			
>			mcnt = mcnt+1
>			
>		ENDCASE
>		
>
>	Endfor  && field scan
>	
>		lcsql = lcsql + " FROM "+ lcalias + " INTO CURSOR C_"+lcalias
>		
>&lcsql 
>
>cname = "C_"+lcalias
>
>SELECT (cname)
>
>lcexpstr = [ COPY TO ']+lcexppath+cname+[' DELIMITED WITH "" WITH CHARACTER '|' ] 
>
>&lcexpstr 
>
>					
>		
>		SELECT tablesql 
>		LOCATE FOR ALLTRIM(tablename) = lcalias
>		replace sql WITH lcsql
>		
>
>
>		lcsql = "SELECT " +lf
>
>
>Endfor && tablescan
>
>Close Database
>
>**********************************************************************************************************************
>
>Set Default To (lcdefa)
>
>Return
>
>
>
>