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