Many times we need to update a record with new data in a table, if the primary/unique key already exists and if not insert the data. If it is one or a few records, we can do that by writing and executing simple sql statements for each record.
When we need to update/insert thousands of records, performance using the approach is unacceptable.
We can obtain better performance using the MERGE syntax. Let us use this approach with a simple sample. Let us also use the Demo Server provided by FWH.
We have a table "STATES" with three fields ID (autoinc), CODE (VarChar(2)) and NAME (VarChar(25)).
We have this latest data of CODEs and NAMEs.
- Code: Select all Expand view
{ { "MT", "MONTANA NEW" } ;
, { "WA", "WASHINGTON NEW" } ;
, { "ME", "MAINE NEW" } ;
, { "A2", "NEW NAME" } ;
, { "A3", "OTHER NAME" } ;
}
We want to update the STATES table with this new information, by updating NAME where the CODE already exists and otherwise insert the new CODE and NAMEs.
Example:
- Code: Select all Expand view
#include "fivewin.ch"
#include "adodef.ch"
//----------------------------------------------------------------------------//
function Main()
local oCn, oRs, cSql
local aData
? "Connect to FWH MSSQL DemoServer"
oCn := FW_OpenAdoConnection( "MSSQL,208.91.198.196,gnraore3_,fwhmsdemo,fwh@2000#", .t. )
if oCn == nil
? "Failed to connect"
return nil
endif
? "Connected"
? FW_AdoImportFromDBF( oCn, "C:\FWH\SAMPLES\STATES.DBF" )
aData := { { "MT", "MONTANA NEW" } ;
, { "WA", "WASHINGTON NEW" } ;
, { "ME", "MAINE NEW" } ;
, { "A2", "NEW NAME" } ;
, { "A3", "OTHER NAME" } ;
}
// has to be PRIVATE not local
PRIVATE cValues := "( VALUES " + SubStr( FW_ValToSQL( aData ), 2 )
TEXT INTO cSql
MERGE INTO states AS Target
USING &cValues
AS Source (NewCode, NewName)
ON Target.Code = Source.NewCode
WHEN MATCHED THEN
UPDATE SET name = Source.NewName
WHEN NOT MATCHED BY TARGET THEN
INSERT (code,name) VALUES (NewCode, NewName);
ENDTEXT
MEMOEDIT( cSql ) // If you like to see the full sql statement
oCn:Execute( cSql )
oRs := FW_OpenRecordSet( oCn, "states" )
XBROWSER oRs
oCn:Close()
return nil
Copy this program to your fwh\samples folder and build and run with buildh.bat or buildx.bat
We have used only 5 rows of data in this sample. You may try with a few thousands of records with your own server.
