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