Sep 24, 2013

The operation 'MERGE' is not supported with memory optimized tables

Suppose we have two memory optimized tables:

CREATE TABLEtblEmpSource(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)


CREATE TABLEtblEmpTarget(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

Now we want to perform merge operation. When we will execute this query:

MERGE tblEmpTarget T
USING tblEmpSource S
ON T.ntEmpID = S.ntEmpID
WHEN MATCHED THEN UPDATE SET vcName = S.vcName,moSalary = S.moSalary
WHEN NOT MATCHED THEN INSERT(ntEmpID,vcName,moSalary) VALUES(S.ntEmpID,S.vcName,S.moSalary);

We may get error message something like this:

Msg 10770, Level 16, State 110, Line 15
The operation 'MERGE' is not supported with memory optimized tables.

Cause: Sql server 2014 doesn't support merge operation between two or one memory optimized tables.

Solution: We will have to perform all the merge operation by different script. For example:

--To Update
UPDATE tblEmpTarget SET vcName = S.vcName,moSalary = S.moSalary
FROM tblEmpSource S
WHERE S.ntEmpID = tblEmpTarget.ntEmpID

--To insert
INSERT INTOtblEmpTarget(ntEmpID,vcName,moSalary)
SELECT S.ntEmpID,S.vcName,S.moSalary
FROM tblEmpSource S INNERJOIN tblEmpTarget T

ON S.ntEmpID <> T.ntEmpID

No comments:

Post a Comment