MSSQL UPSERT/MERGE
Posted: Thu Aug 08, 2019 9:40 am
Upsert (Update or Insert) in MSSQL is achieved using MERGE syntax.
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.
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:
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.
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
{ { "MT", "MONTANA NEW" } ;
, { "WA", "WASHINGTON NEW" } ;
, { "ME", "MAINE NEW" } ;
, { "A2", "NEW NAME" } ;
, { "A3", "OTHER NAME" } ;
}
Example:
Code: Select all
#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
We have used only 5 rows of data in this sample. You may try with a few thousands of records with your own server.