CLONING A DATABASE

CREATE TABLE TBL_OBJTYPE(OB_TYPE_ID INT PRIMARY KEY,TYPENAME VARCHAR(100))

INSERT INTO TBL_OBJTYPE VALUES (1,'DRIVE'),
(2,'FOLDER'),
(3,'FILE')
-----------------------------------------------------------------------------

CREATE TABLE TBL_OBJECTS(OID INT PRIMARY KEY,NAME VARCHAR(100),
OTID INT REFERENCES TBL_OBJTYPE(OB_TYPE_ID),POID INT REFERENCES TBL_OBJECTS(OID))


INSERT INTO TBL_OBJECTS VALUES(1,'C:\',1,NULL) ,
(2,'D:\',1,NULL),
(3,'MYCOMP',2,1),
(4,'A.DOC',3,3),
(5,'B.DOC',3,3),
(6,'MOVIES',2,3),
(7,'SOFTWARES',2,2),
(8,'EDU',2,7),
(9,'C.TXT',3,8),
(10,'D.TXT',3,8)

----------------------------------------------------------------------------
SELECT * FROM TBL_OBJECTS

WITH Y AS (SELECT * FROM TBL_OBJECTS WHERE OID=8
UNION ALL
SELECT E.* FROM TBL_OBJECTS E INNER JOIN Y ON E.OID=Y.POID)
SELECT * FROM Y

-----------------------------------------------------------------------------
CREATE PROC USP_CLONE(@OID INT,@POID INT) AS
BEGIN

DECLARE @OLD_POID INT
SELECT @OLD_POID = POID FROM TBL_OBJECTS WHERE OID=@OID

SELECT * INTO #TEMP FROM TBL_OBJECTS WHERE 1=2
ALTER TABLE #TEMP ADD NEW_OID INT IDENTITY(1,1)
DECLARE @MAXOID INT
SELECT @MAXOID=MAX(OID)+1 FROM TBL_OBJECTS
DBCC CHECKIDENT ('TEMPDB.DBO.#TEMP',RESEED,@MAXOID)

;WITH X AS(
SELECT * FROM TBL_OBJECTS WHERE OID=@OID
UNION ALL
SELECT T.* FROM X INNER JOIN TBL_OBJECTS T ON X.OID=T.POID)

INSERT INTO #TEMP
SELECT * FROM X

ALTER TABLE #TEMP ADD NEW_POID INT

UPDATE B SET NEW_POID=A.NEW_OID FROM #TEMP A INNER JOIN #TEMP B ON A.OID = B.POID

UPDATE  #TEMP SET NEW_POID=@POID WHERE POID=@OLD_POID
SELECT * FROM #TEMP
INSERT INTO TBL_OBJECTS
SELECT NEW_OID , NAME,OTID,NEW_POID FROM #TEMP
END

EXEC USP_CLONE 3,1