Basically it works by ordering the data in the source table and assiging a Row number to each of the source rows (Select in the CTE)

Then a Left Join with sub select that counts the rows in the Destination table and returns a coutn value.

So if we only want a max of 3 rows, if the Row Number from the first query + the count from the sub select <3 it will insert rows where RN + Count <3.

so

Id 1, 0000000, RN=1

Id 2, 1111111, RN=1

Id 3, 1111111, RN=2

Id 4, 1111111, RN=3

If we have no rows in the destination table, then the count will be NULL (hence the IsNULL(e_cnt,0)), so RN-0<3 will insert all 4 rows.

On a second pass the e_cnt will be 1 for 0000000 and 3 for 1111111, so for 0000000 (RN-1)<3 = true but for 1111111 the e_cnt is 3, so RN-3 <3 is false.

Hope that makes sense.

you can run each the CTE select and sub select on the join individually to see how it works with a populated and unpopulated table.