Ok, sorry, I've never used BIML. Also I've only used memory optimised table once, and I found...

You cannot have DATA_COMPRESSION on memory optimised table, so there is no point in specifying it.

So remove that option and just leave: WITH (MEMORY_OPTIMIZED=ON)

You just need BEGIN/END around the statement.

Also, it will be more efficient to use EXISTS than SELECT COUNT(*).

IF EXISTS(SELECT * FROM [A]) BEGIN

  ...

You just need BEGIN/END around the statement.

Also, it will be more efficient to use EXISTS than SELECT COUNT(*).

IF EXISTS(SELECT * FROM [A]) BEGIN

    TRUNCATE...

Have you tried running taskmgr or perfmon to see which processes are using a lot of memory?

I don't think you can come...

SELECT p.ID PRODUCT_ID, 
       p.PRODUCT_NAME, 
       o.ID ORD_ID, 
      ...

It could be that you are not limiting the maximum amount of memory available to SQL Server?

If you right-click on the server in the SSMS object explorer, then on the...

I can't see how you know exactly where to split the number, for example, how would you know if tbl2 number 1234050360 is 1234-050360 or 123405-0360?

If you are sure you...

Do you have an index on CQRReportInstance(ReportInstanceId) ?

If you use INSENSITIVE when you create the cursor it will make a copy of the data used by the cursor in tempdb...

This *was* good advice back in 2005, but now on SQL Server 2012 and above, table variables can be indexed, are dynamically indexed by default and fully accessible by...

Of course, that recommendation also comes with the warning that if the hashbytes are different, then the rows are guaranteed to be different.  However (and you know...

First, if you add a few new-lines the query becomes readable:

SELECT t.PartNumber, 
       t.CompanyName, 
      ...

Of course, that recommendation also comes with the warning that if the hashbytes are different, then the rows are guaranteed to be different.  However (and you know this but...

SELECT CONCAT('
SELECT ''', QUOTENAME(t.TABLE_SCHEMA), '.', QUOTENAME(t.TABLE_NAME),  ''' [TABLE_NAME], COUNT(*) COUNT 
  FROM (SELECT ',x.c, '
          FROM...