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