Recursive in SQL Server 2008

Question

I've a scenario(table) like this:

This is table(Folder) structure. I've only records for user_id = 1 in this table. Now I need to insert the same folder structure for another user.

Sorry, I've updated the question... yes, folder_id is identity column (but folder_id can be meshed up for a specific userID). Considering I don't know how many child folder can exists. Folder_Names are unique for an user and Folder structures are not same for all user. Suppose user3 needs the same folder structure of user1, and user4 needs same folder structure of user2. and I'll be provided only source UserID and destination UserID(assume destination userID doesn't have any folder structure).

How can i achieve this?

Answer 1

You can do the following:

SET IDENTITY_INSERT dbo.Folder ON
go

declare @maxFolderID int
select @maxFolderID = max(Folder_ID) from Folder

insert into Folder
select @maxFolderID + FolderID, @maxFolderID + Parent_Folder_ID, Folder_Name, 2
from Folder
where User_ID = 1

SET IDENTITY_INSERT dbo.Folder OFF
go

EDIT:

SET IDENTITY_INSERT dbo.Folder ON
GO

;
WITH    m AS ( SELECT   MAX(Folder_ID) AS mid FROM     Folder ),
        r AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY Folder_ID ) + m.mid AS rn
               FROM     Folder
                        CROSS JOIN m
               WHERE    User_ID = 1
             )
    INSERT  INTO Folder
            SELECT  r1.rn ,
                    r2.rn ,
                    r1.Folder_Name ,
                    2
            FROM    r r1
                    LEFT JOIN r r2 ON r2.Folder_ID = r1.Parent_Folder_ID


SET IDENTITY_INSERT dbo.Folder OFF
GO

Answer 2

This is as close to set-based as I can make it. The issue is that we cannot know what new identity values will be assigned until the rows are actually in the table. As such, there's no way to insert all rows in one go, with correct parent values.

I'm using MERGE below so that I can access both the source and inserted tables in the OUTPUT clause, which isn't allowed for INSERT statements:

declare @FromUserID int
declare @ToUserID int
declare @ToCopy table (OldParentID int,NewParentID int)
declare @ToCopy2 table (OldParentID int,NewParentID int)

select @FromUserID = 1,@ToUserID = 2

merge into T1 t
using (select Folder_ID,Parent_Folder_ID,Folder_Name
       from T1 where User_ID = @FromUserID and Parent_Folder_ID is null) s
on 1 = 0
when not matched then insert (Parent_Folder_ID,Folder_Name,User_ID)
                      values (NULL,s.Folder_Name,@ToUserID)
output s.Folder_ID,inserted.Folder_ID into @ToCopy (OldParentID,NewParentID);

while exists (select * from @ToCopy)
begin
    merge into T1 t
    using (select Folder_ID,p2.NewParentID,Folder_Name from T1
           inner join @ToCopy p2 on p2.OldParentID = T1.Parent_Folder_ID) s
    on 1 = 0
    when not matched then insert (Parent_Folder_ID,Folder_Name,User_ID) 
                          values (NewParentID,Folder_Name,@ToUserID)
    output s.Folder_ID,inserted.Folder_ID into @ToCopy2 (OldParentID,NewParentID);

    --This would be much simpler if you could assign table variables,
    -- @ToCopy = @ToCopy2
    -- @ToCopy2 = null
    delete from @ToCopy;
    insert into @ToCopy(OldParentID,NewParentID)
        select OldParentID,NewParentID from @ToCopy2;
    delete from @ToCopy2;
end

(I've also written this on the assumption that we don't ever want to have rows in the table with wrong or missing parent values)

---

In case the logic isn't clear - we first find rows for the old user which have no parent - these we can clearly copy for the new user immediately. On the basis of this insert, we track what new identity values have been assigned against which old identity value.

We then continue to use this information to identify the next set of rows to copy (in @ToCopy) - as the rows whose parents were just copied are the next set eligible to copy. We loop around until we produce an empty set, meaning all rows have been copied.

This doesn't cope with parent/child cycles, but hopefully you do not have any of those.

Answer 3

Assuming Folder.Folder_ID is an identity column, you would be best off doing this in two steps, the first step is to insert the folders you need, the next is to update the parent folder ID.

DECLARE @ExistingUserID INT = 1,
        @NewUserID INT = 2;

BEGIN TRAN;

-- INSERT REQUIRED FOLDERS
INSERT Folder (Folder_Name, User_ID)
SELECT  Folder_Name, User_ID = @NewUserID
FROM    Folder
WHERE   User_ID = @ExistingUserID;

-- UPDATE PARENT FOLDER
UPDATE  f1
SET     Parent_Folder_ID = p2.Folder_ID
FROM    Folder AS f1
        INNER JOIN Folder AS f2
            ON f2.Folder_Name = f1.Folder_Name
            AND f2.user_id = @ExistingUserID
        INNER JOIN Folder AS p1
            ON p1.Folder_ID = f2.Parent_Folder_ID
        INNER JOIN Folder AS p2
            ON p2.Folder_Name = p1.Folder_Name
            AND p2.user_id = @NewUserID
WHERE   f1.user_id = @NewUserID;

COMMIT TRAN;

---

Solution 2

DECLARE @Output TABLE (OldFolderID INT, NewFolderID INT, OldParentID INT);

DECLARE @ExistingUserID INT = 1,
        @NewUserID INT = 2;

BEGIN TRAN;

MERGE Folder AS t
USING
(   SELECT  *
    FROM    Folder
    WHERE   user_ID = @ExistingUserID
) AS s
    ON 1 = 0 -- WILL NEVER BE TRUE SO ALWAYS GOES TO MATCHED CLAUSE
WHEN NOT MATCHED THEN 
    INSERT (Folder_Name, User_ID)
    VALUES (s.Folder_Name, @NewUserID)
OUTPUT s.Folder_ID, inserted.Folder_ID, s.Parent_Folder_ID 
    INTO @Output (OldFolderID, NewFolderID, OldParentID);

-- UPDATE PARENT FOLDER
UPDATE  f
SET     Parent_Folder_ID = p.NewFolderID
FROM    Folder AS f
        INNER JOIN @Output AS o
            ON o.NewFolderID = f.Folder_ID
        INNER JOIN @Output AS p
            ON p.OldFolderID = o.OldParentID;

COMMIT TRAN;