Does Encryption columns (Deterministic/Randomized) support Merge SQL statement inside stored Procedure? If not, how to replace merge SQL statement with other code?

Khwaza Bandenawaz Sikkalagar 0 Reputation points
2024-06-18T08:31:32.7833333+00:00

There are 2- encrypted (Deterministic & Randomized) tables And I am loading data into Table1 via SSIS package. And after that, I am using Stored Procedure to merge data from table1 to table2 based on matches - INSERT, UPDTAE OR DELETE.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,264 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 29,012 Reputation points Microsoft Employee
    2024-06-19T15:54:18.6233333+00:00

    @Khwaza Bandenawaz Sikkalagar Welcome to Microsoft Q&A thanks for posting your question.

    Your question is more of SQL server not Azure SQL Database, I have added the right tag.

    When you have tables with encrypted columns using SQL Server's Always Encrypted feature, there are some limitations on the operations you can perform directly on the encrypted data. Merge statement, which is used to synchronize two tables by inserting, updating, or deleting records based on certain conditions, is one of the operations that is not supported on encrypted columns

    May be you can try this , however i have not tested yet

    -- Update existing records

    UPDATE table2

    SET column1 = table1.column1, column2 = table1.column2, ...

    FROM table1

    WHERE table2.key_column = table1.key_column

    Regards

    Geetha

    0 comments No comments