Copy attributes of parent and child

Question

I'm having trouble figuring out the most logical way to clone parent / child product categories for another company.

There are 3 levels of categories.

i.e. Mens Clothing(L1)> Shirt(L2)> Short Sleeve(L3)
( Shirtis the parent element Short Sleeve, but Mens Clothingis the parent Shirt)

ParentCategoryIdis the category identifier immediately above.

ParentStringis the identifier of all categories above, in level order.

(i.e., for the product L3, it will be Id for L1, then Id for L2)
 The table setting is as follows.

CREATE TABLE #Categories
(
CategoryId INT
,CompanyId INT
,ParentCategoryId INT
,CategoryName VARCHAR(255)
,ParentString VARCHAR(20)
)
INSERT INTO #Categories VALUES
(123, 12, NULL, 'Mens Clothing',     NULL),
(124, 12, 123,  'Shirt',            '123-'),
(125, 12, NULL, 'Womens Clothing',  NULL),
(126, 12, 125,  'Shirt',            '125-'),
(127, 12, 124,  'Short Sleeve',     '123-124-'),
(128, 12, NULL, 'Drinks',           NULL),
(129, 12, 128,  'Water',            '128-')

I need to copy all categories and levels to CompanyId13.

Answer 1

.

• CTE 12,

• MAX id , ROW_NUMBER(),

• ParentString XML,

• , ,

(. ), Categories:

DECLARE @id int 

SELECT @id = MAX(CategoryId) FROM #Categories

;WITH cte AS (
SELECT  ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY CategoryId) + @id as NewCategoryId,
        CategoryId as OldCategoryId,
        CompanyId,
        ParentCategoryId,
        CategoryName,
        CAST('<p>'+REPLACE(STUFF(ParentString,LEN(ParentString),1,''),'-','</p><p>')+'</p>' as xml) parentxml
FROM #Categories
WHERE CompanyId = 12
)

SELECT  c.NewCategoryId,
        13 as CompanyId,
        c1.NewCategoryId as NewParentCategoryId,
        c.CategoryName,
        (
        SELECT CAST(f.NewCategoryId as nvarchar(max)) +'-'
        FROM cte f
        OUTER APPLY (
            SELECT  t.c.value('.','int') as xpart
            FROM c.parentxml.nodes('/p') as t(c)
        ) x
        WHERE OldCategoryId = x.xpart
        FOR XML PATH('')
        ) as ParentString
FROM cte c
LEFT JOIN cte c1 
    ON c1.OldCategoryId = c.ParentCategoryId

:

NewCategoryId   CompanyId   NewParentCategoryId CategoryName    ParentString
130             13          NULL                Mens Clothing   NULL
131             13          130                 Shirt           130-
132             13          NULL                Womens Clothing NULL
133             13          132                 Shirt           132-
134             13          131                 Short Sleeve    130-131-
135             13          NULL                Drinks          NULL
136             13          135                 Water           135-

CategoryId - IDENTITY, IDENTITY_INSERT :

SET IDENTITY_INSERT Categories ON
BEGIN TRANSACTION
...
COMMIT TRANSACTION
SET IDENTITY_INSERT Categories OFf

Answer 2

, XML . cteMap2 , . , , . , XML .

cteMap1 , #Categories ( )

cteMap2 , , , .

xml, (Insert Into...)

Declare @OldCoID int = 12
Declare @NewCoID int = 55

Declare @String varchar(max) = ((Select * from #Categories Where CompanyId=@OldCoID For xml raw))

;with cteMap1 as (
        Select Old  = CategoryID
              ,New  = (Select max(CategoryID) from #Categories) + Row_Number() over (Order By CategoryID)
         From  #Categories
         Where CompanyID = @OldCoID)
    , cteMap2 as (
        Select Old=concat('CategoryId="',Old,'"'),New=concat('CategoryId="',New,'"') From cteMap1
        Union All
        Select Old=concat('ParentCategoryId="',Old,'"'),New=concat('ParentCategoryId="',New,'"') From cteMap1
        Union All
        Select Old=concat('CompanyId="',@OldCoID,'"'),New=concat('CompanyId="',@NewCoID,'"')
        Union All
        Select Old=concat(Old,'-'),New=concat(New,'-') From cteMap1
    )
Select @String = Replace(@String,Old,New) From cteMap2 
Declare @XML xml = @String

Select CategoryId       = xRow.value('@CategoryId[1]','int')
      ,CompanyId        = xRow.value('@CompanyId[1]','int')
      ,ParentCategoryId = xRow.value('@ParentCategoryId[1]','int')
      ,CategoryName     = xRow.value('@CategoryName[1]','varchar(255)')
      ,ParentString     = xRow.value('@ParentString[1]','varchar(20)')
From  @XML.nodes('/row') As A(xRow)

CategoryId  CompanyId   ParentCategoryId    CategoryName    ParentString
130         55          NULL                Mens Clothing   NULL
131         55          130                 Shirt           130-
132         55          NULL                Womens Clothing NULL
133         55          132                 Shirt           132-
134         55          131                 Short Sleeve    130-131-
135         55          NULL                Drinks          NULL
136         55          135                 Water           135-

Answer 3

.

declare @shift  int = (select max(CategoryId) from #Categories);
-- data for new company 14 
INSERT  #Categories(
SELECT @shift + CategoryId, 14 companyId, @shift + ParentCategoryId, CategoryName, NULL as ParentString
FROM  #Categories
WHERE companyId = 12;

ParentString , .