Copy Data Using OrganisationChart

Transact-SQL
1

Hi,

In a web application, I have a tree view which uses a self referencing table (ID, PARENTID)

I use the sproc below to delete pages and also their related child pages. It works as expected.

I'd like to be able to COPY a page as well as all its child pages and insert them into them table.
I guess I'll need some sort of loop but not sure how??
All help appreciated.
Cheers, Jack

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TreeKB_DeleteSiteConByPageID]

(
@id int
)

AS

WITH OrganisationChart (Id, parentid)
as
(SELECT Id, parentid
FROM TreeKB_tbl_Content
WHERE id = @id -- pass in the variable here
UNION ALL
SELECT sc.Id, sc.parentid
FROM TreeKB_tbl_Content sc INNER JOIN OrganisationChart
ON sc.parentid = OrganisationChart.Id)

DELETE FROM TreeKB_tbl_Content
FROM TreeKB_tbl_Content
INNER JOIN OrganisationChart
ON TreeKB_tbl_Content.Id = OrganisationChart.Id

RETURN
2

Please provide:

  • table description as create statement
  • sample data as insert statement
  • expected output based on your sample data