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