When I test this code using a table with three columns, it works. When I test it against the production table with 100 columns, it fails with SQLSTT=42802.
MERGE INTO ARP001 USING (VALUES ('K2', 'OFFICE K2', 'K2'))
NewData (CMCUST, CMNAME, CMTID)
ON ARP001.CMCUST = NewData.CMCUST
WHEN NOT MATCHED THEN
INSERT VALUES(NewData.CMCUST, NewData.CMNAME, NewData.CMTID)
WHEN MATCHED THEN
UPDATE SET CMNAME = NewData.CMNAME, CMTID = NewData.cmtid;
The error text states, "If any columns not specified in the INSERT column list were created as NOT NULL, specify values for those columns." Since this table is created with NOT NULL, it appears I can't use MERGE for a one-statement add/update operation unless I provide defaults for the 97 unreferenced columns. Is this interpretation correct?
I'm trying to figure out an alternate solution that will work in high-concurrency environment (think inventory transactions). Unexpected activity can slip between an UPDATE and an INSERT, so I suspect something like SET TRANSACTION ISOLATION LEVEL SERIALIZABLE will be necessary to make things work properly.
I'd appreciate any solutions to the insert/update process!