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:
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)
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
tempo2sql(dbtmp7,tRepos,cField)
And this is the Function that creates, inserts and deletes the temporal table (quite complicated, isn't it?)
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.
