Thanks MR. Rao, both tables have the very same structure, but both have a ID column generated when I imported the data from my old DBF's, therefore I cannot just insert one table into another because I got an error, instead of that I generate one temporal DBF, export it to a table and the copy the fields, except the one with the ID, I know it sound much more complicated that it shuld be, but it was the workaround I found.
To acomplish that I use the next functions:
First I generate the tempral DBF to be send to SQL:
Code: Select all
copy to &dbtmp7 fields field1,field2,field3,...
then I create a variable containing the fields I want to add to the real database (I do this because when exporting the DBF to SQL the table will be created with the ID field automatically)
Code: Select all
cField:="field2,field2,field3,...."
Then I call my function that inserts the DBF to SQL, adds just the fields that I need to insert into the real table an then drops the temporal table, passing 3 parameters, the temporal DBF, the real SQL table and the list of fields to be copied
And this is the Function that creates, inserts and deletes the temporal table (quite complicated, isn't it?)
Code: Select all
Function tempo2sql(tempdb,realdb,cField0)
local oCn
cTemporal:=tempdb
cRealdb:=realdb
cFields=cField0
cTabtem:=cFilename(cTemporal)
close all
dbtempo:=alltrim(cTemporal)+".dbf"
oCn := FW_OpenAdoConnection( { "MSSQL", xSOURCE, xCATALOGA, xUSERID, xPASSWORD }, .t. )
if oCn == nil
? "Failed to connect"
return .f.
endif
if FW_AdoImportFromDBF( oCn, "&dbtempo" )
lImported=.T.
else
? "Import Fail"
lImported:=.F.
endif
oCn:Close()
if lImported=.T.
cCadsql0:="insert into &cRealdb select &cFields from &cTabtem"
oRs0 := TOleAuto():New( "ADODB.Recordset" )
oRs0:CursorType := 1 // opendkeyset
oRs0:CursorLocation := 3 // local cache
oRs0:LockType := 3 // lockoportunistic
TRY
cursorwait()
oRS0:Open( cCadSql0,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOGA+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error inserting data to the real SQL table" )
RETURN(.F.)
END TRY
cCadsql01:="drop table &cTabtem"
oRs01 := TOleAuto():New( "ADODB.Recordset" )
oRs01:CursorType := 1 // opendkeyset
oRs01:CursorLocation := 3 // local cache
oRs01:LockType := 3 // lockoportunistic
TRY
cursorwait()
oRS01:Open( cCadSql01,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOGA+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error dropping temporal table" )
RETURN(.F.)
END TRY
endif
return nil
Eventhough it is working , I know there has to be a simpler way to do this.