CREATE TABLE #xms_staff_department (  id int,  name varchar(128),  parent_id int,  parent_path varchar(512) ,  depth smallint,  is_delete tinyint,  sort smallint)INSERT INTO #xms_staff_departmentSELECT id,name,0,‘0,‘+CONVERT(VARCHAR,id),1, (CASE WHEN IsVaild = 1 THEN 0 ELSE 1 END), dOrder FROM dept WHERE Pid = 0DECLARE @id				INTDECLARE @parent_path	VARCHAR(128)DECLARE @cursor1			CURSORSET @cursor1 = CURSOR FORWARD_ONLY STATIC FORSELECT id, parent_path FROM #xms_staff_departmentOPEN @cursor1 FETCH NEXT FROM @cursor1 INTO @id, @parent_path WHILE (@@FETCH_STATUS = 0)BEGIN		INSERT INTO #xms_staff_department	SELECT id,name,Pid, @parent_path+‘,‘+CONVERT(VARCHAR,id),2, (CASE WHEN IsVaild = 1 THEN 0 ELSE 1 END), dOrder FROM dept WHERE Pid = @id		DECLARE @cursor2			CURSOR	DECLARE @id2				INT	DECLARE @parent_path2	VARCHAR(128)	SET @cursor2 = CURSOR FORWARD_ONLY STATIC FOR	SELECT id, parent_path FROM #xms_staff_department WHERE parent_id = @id	OPEN @cursor2 	FETCH NEXT FROM @cursor2 INTO @id2, @parent_path2 	WHILE (@@FETCH_STATUS = 0)	BEGIN 				INSERT INTO #xms_staff_department		SELECT id,name,Pid, @parent_path+‘,‘+CONVERT(VARCHAR,id),3, (CASE WHEN IsVaild = 1 THEN 0 ELSE 1 END), dOrder FROM dept WHERE Pid = @id2						DECLARE @cursor3			CURSOR		DECLARE @id3				INT		DECLARE @parent_path3	VARCHAR(128)		SET @cursor3 = CURSOR FORWARD_ONLY STATIC FOR		SELECT id, parent_path FROM #xms_staff_department WHERE parent_id = @id2		OPEN @cursor3 		FETCH NEXT FROM @cursor3 INTO @id3, @parent_path3 		WHILE (@@FETCH_STATUS = 0)		BEGIN						INSERT INTO #xms_staff_department			SELECT id,name,Pid, @parent_path+‘,‘+CONVERT(VARCHAR,id),4, (CASE WHEN IsVaild = 1 THEN 0 ELSE 1 END), dOrder FROM dept WHERE Pid = @id3						DECLARE @cursor4			CURSOR			DECLARE @id4				INT			DECLARE @parent_path4	VARCHAR(128)			SET @cursor4 = CURSOR FORWARD_ONLY STATIC FOR			SELECT id, parent_path FROM #xms_staff_department WHERE parent_id = @id3			OPEN @cursor4 			FETCH NEXT FROM @cursor4 INTO @id4, @parent_path4 			WHILE (@@FETCH_STATUS = 0)			BEGIN								INSERT INTO #xms_staff_department				SELECT id,name,Pid, @parent_path+‘,‘+CONVERT(VARCHAR,id),5, (CASE WHEN IsVaild = 1 THEN 0 ELSE 1 END), dOrder FROM dept WHERE Pid = @id4										FETCH NEXT FROM @cursor4 INTO @id4, @parent_path4			END			CLOSE @cursor4			DEALLOCATE @cursor4												FETCH NEXT FROM @cursor3 INTO @id3, @parent_path3 		END		CLOSE @cursor3		DEALLOCATE @cursor3									FETCH NEXT FROM @cursor2 INTO @id2, @parent_path2	END	CLOSE @cursor2	DEALLOCATE @cursor2		FETCH NEXT FROM @cursor1 INTO @id, @parent_path ENDCLOSE @cursor1DEALLOCATE @cursor1select * from #xms_staff_department
drop table #xms_staff_department