In Azure SQL Database, I have a MERGE INTO USING(VALUES... query, which should merge about 8000 lines of values into a table. I am getting the following error, when I try to execute the query:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

The pricing tier of the database is S1 (20 DTUs). I have tried increasing it up to S6 (400 DTUs) and I still get the same error. When I had about 6000 lines of values, I had the same issue. However, back then I was using S0 (10 DTUs) and increasing it to S1(20 DTUs) worked for some more values. It seems that now, increasing the pricing tier does not help. I can't simplify the query as this is a simple MERGE INTO post-deployment script. What should I do?

CodePudding user response：

What should I do?

You must ensure that no TSQL query is too long. You can have lots of queries, or long string literals, but not very long queries.

So use JSON or XML stored in a string literal, and then OPENJSON/xml.Nodes to shred it.

or

Create a temp table and load it using multiple INSERT statements and then perform the upsert.