CREATE TABLE [dbo].[WorkOrders]
(
[WorkOrderID] [int] NOT NULL IDENTITY(1, 1),
[SalesOrderDetailsID] [int] NOT NULL,
[BillOfWorkID] [int] NOT NULL ,
[ResourceID] [int] NOT NULL ,
[WorkOrderStatusID] [smallint] NOT NULL ,
[CreatedByWorkOrderID] [int] NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrders] ADD CONSTRAINT [PK_WorkOrders] PRIMARY KEY CLUSTERED  ([WorkOrderID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrders] ADD CONSTRAINT [FK_WorkOrders_WorkOrders] FOREIGN KEY ([CreatedByWorkOrderID]) REFERENCES [dbo].[WorkOrders] ([WorkOrderID])
GO

• I have a foreign key so I can't just delete - I need to delete with a particular order.


• I can't use "on delete cascade" because it will delete the dummy work order.


• I don't want to use cursors due to their problems.


• There are a few millions records in this table.


• I tried deleting based on finding the max work order id per sales order details, delete it and do it again using a while loop.
  It works, but it took 6 hours, and I need to run it daily ...

ALTER TABLE workorders add OrgNode hierarchyid, level int

WITH paths(WorkOrderID, level) 
AS (
	-- This section provides the value for the root of the hierarchy
	SELECT WorkOrderID, level = 0
	FROM WorkOrders AS C 
	WHERE CreatedByWorkOrderID = -1
		AND WorkOrderID > -1
	UNION ALL 
	-- This section provides values for all nodes except the root
	SELECT C.WorkOrderID, level = p.level + 1
	FROM WorkOrders AS C 
		JOIN paths AS p 
			ON C.CreatedByWorkOrderID = P.WorkOrderID
)
UPDATE w SET level = p.level
FROM paths p
	INNER JOIN WorkOrders w
		on p.WorkOrderID = w.WorkOrderID

SELECT * from WorkOrders

DECLARE @MaxLevel int = (SELECT MAX(Level) FROM WorkOrders)

WHILE (@MaxLevel > 0)
	BEGIN
		DELETE FROM WorkOrders WHERE Level = @MaxLevel AND ---> Some more delete criteria here
		
		SET @MaxLevel = @MaxLevel - 1
	END

 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

WITH paths(WorkOrderID, level) 
AS (
	-- This section provides the value for the root of the hierarchy
	SELECT WorkOrderID, level = 0
	FROM WorkOrders AS C 
	WHERE CreatedByWorkOrderID = -1
		AND WorkOrderID <> CreatedByWorkOrderID
        ...

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tblForeignKeys')
BEGIN
	CREATE TABLE tblForeignKeys (ID INT IDENTITY(1,1), FK_name VARCHAR(255), Parent_object varchar(255), 
	Reference_object varchar(255), DropConstraint VARCHAR(max), CreateConstraint VARCHAR(max), 
	CheckConstraint VARCHAR(max)
	 )
END 

declare @min int
declare @max int
declare @debug bit
declare @cmd nvarchar(4000)

INSERT INTO tblForeignKeys (FK_name, Parent_object, Reference_object, DropConstraint,
CreateConstraint, CheckConstraint)
SELECT fk.name, object_name(fc.parent_object_id), object_name(fc.referenced_object_id),
	'ALTER TABLE ['+ object_name(fc.parent_object_id) +'] DROP CONSTRAINT ['+ fk.name +']',
	'ALTER TABLE '+ object_name(fc.parent_object_id) +'  WITH NOCHECK ADD  CONSTRAINT ['+ fk.name +'] FOREIGN KEY(['+ pc.name +'])
	REFERENCES ['+ object_name(fc.referenced_object_id) +'] (['+ rc.name +'])
	NOT FOR REPLICATION',
	'ALTER TABLE ['+ object_name(fc.parent_object_id) +'] CHECK CONSTRAINT ['+fk.name+']'
FROM sys.foreign_keys fk
	INNER JOIN sys.foreign_key_columns fc on fk.object_id = fc.constraint_object_id
	INNER JOIN sys.columns pc on fc.parent_object_id = pc.object_id and fc.parent_column_id = pc.column_id
	INNER JOIN sys.columns rc on fc.referenced_object_id = rc.object_id and fc.referenced_column_id = rc.column_id
--WHERE object_name(fc.referenced_object_id) = 'users'


select * from tblForeignKeys

SET NOCOUNT ON
GO
UPDATE WorkOrders SET Level = 1 WHERE CreatedByWorkOrderID = -1
GO
WHILE EXISTS (SELECT 1 FROM WorkOrders WHERE Level IS NULL)
	BEGIN
		UPDATE child SET Level = parent.Level + 1
		FROM WorkOrders child
			INNER JOIN WorkOrders parent
				ON child.CreatedByWorkOrderID = parent.WorkOrderID
		WHERE parent.Level IS NOT NULL
			AND child.Level IS NULL
		
		--> If you want to give the process a breather between each iteration
		--> and to open up for other processes to do some work
		--> WAITFOR DELAY '00:00:05'
	END
GO