MSSQL UPSERT/MERGE

[nageswaragunupudi]

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.

Code: Select all

{ { "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

#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.