phingers (9/2/2014)
Thanks, that works great on my sample data, but when I try it against the real table with more rows it returns the following error:The maximum recursion 100 has been exhausted before statement completion.
Any ideas how to overcome this?
You can modify the allowed number of iterations by adding the line option OPTION (MAXRECURSION XXX) as the last line for the insert statement. Instead of XXX you should put the number of iterations that you think will be sufficient. If you'll write 0, then there will be no limit to the number of iterations.
Off curse you can use Eirikur Eiriksson's way. I have to admit that I don't understand it yet, and I'll have to take a closer look at it, but it might be more efficient then my way.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/