SQL - Looping - copy category

Assume Category table has categoryId, name and parentCategoryId columns, where categoryId is an identity field.

now what if I have to replicate a category and assign it to a new parent? Because it's not a simple insert statement. I have to insert all the subcategories and their subcategories开发者_如何学Python and so on. How would I keep track of their identity fields? I would need that to assign parentCategoryId to their subcategories when inserting.

Is this question clear?

Not sure if you're asking 2 questions or just 1, i.e. replicating an entire category as a new category (i.e. copying a category) vs. re-assigning an existing category to a new parent - each would be different problems/solutions, but let's start with copying an entire category.

First, if you're using an identity-based column, the ONLY way you can do it without using the "set identity_insert on" option would be to cursor through the entire tree, starting from the root nodes and working down (i.e. insert the top-level category(ies), get the newly created identity values, insert the second-level categories, etc.). If you are in a scenario where you can make use of "set identity_insert on", or if you can replace the use of identities with explicit numbers, then you can leverage the code below.

In this code, you'll notice the use of CTE's, recursive CTE's, and ranking functions, so this assumes Sql 2005 or above. Also, the lvl, path, and cnt columns are simply included for demo purposes you can use to view if you like, not required in any final solution:

declare @root_category_id bigint,
        @start_new_id_value bigint;

-- What category id do we want to move?
select  @root_category_id = 3;
-- Get the current max id and pad a bit...
select  @start_new_id_value = max(categoryId)
from    Category;
select  @start_new_id_value = coalesce(@start_new_id_value,0) + 100;

-- Show our values
select  @root_category_id, @start_new_id_value;

begin tran;
set identity_insert Category on;

-- This query will give you the entire category tree
with subs (catId, parentCatId, catName, lvl, path, new_id, new_parent_id, cnt) as (
    -- Anchor member returns a row for the input manager
    select  catId, parentCatId, catName, 0 as lvl,
            cast(cast(catId as varchar(10)) as varchar(max)) as path,
            @start_new_id_value + row_number() over(order by catId) - 1 as new_id, 
            cast(parentCatId as bigint) as new_parent_id, 
            count(*) over(partition by 0) as cnt
    from    Category
    where   catId = @root_category_id

    union all

    -- recursive member returns next level of children
    select  c.catId, c.parentCatId, c.catName, p.lvl + 1,
            cast(p.path + '.' + cast(catId as varchar(10)) as varchar(max)),
            p.cnt + row_number() over(order by c.catId) + p.new_id - 1 as new_id,
            p.new_id as new_parent_id,
            count(*) over(partition by p.lvl) as cnt
    from    subs as p -- Parent
    join    Category as c -- Child
    on      c.parentCatId = p.catId
)
-- Perform the insert
insert  Category 
        (categoryId, Name, parentCategoryId)
select  s.catId, s.catName, s.parentCatId
from    subs s
--order by path;

set identity_insert Category off;
commit tran;

Interesting question.

If you're in SQL 2005+, I suppose you could build a CTE with the full tree of the category to replicate, which will put it in a temporary location to work with, away from the main table.

Then you can use a cursor to work your way down the tree and update the ID number of the parent ...

Now that I type it, it doesn't seem the most efficient solution. Perhaps you could instead do a fancy SELECT statement, which updates the ID of the parent ID as it's SELECTing?

Are you aware of nested sets? It's an alternative way of representing data in problems like this. I don't know if it would help here, but if you're not aware of it you might want to consider it. http://intelligent-enterprise.informationweek.com/001020/celko.jhtml