Cacher is the code snippet organizer for pro developers

DECLARE @SourceTenderId INT = 6043
DECLARE @SourceFolderId INT = 151988

DECLARE @DestinationTenderId INT = 6044
DECLARE @DestionationRootFolderId INT = 152001

BEGIN TRANSACTION;  

BEGIN TRY  
   

 DECLARE @oldDocuments TABLE (parentId INT , docId INT, docGuid UNIQUEIDENTIFIER, newDocId INT, [Level] INT, [OwnerId] INT);

	-- Insert recursively folders and documents uploded by Buyers in the Tender Root folder
	;WITH [DocumentsToCopy]([ParentId], [DocumentId], [DocumentGuid], [Level], [OwnerId]) AS
	(
		SELECT
			 @DestionationRootFolderId
			,D.[DocumentId]
			,D.[DocumentGuid]
			,1 AS [Level]
			,[OwnerId]
		FROM [dbo].[Documents] D
			INNER JOIN [dbo].[Users] U ON U.[UserId] = D.[OwnerId]
			INNER JOIN [dbo].[TenderCompanies] TC ON TC.[TenderId] = @SourceTenderId AND TC.[CompanyId] = U.[CompanyId] AND TC.[RoleId] = 2/*Buyer*/
		WHERE [ParentId] = @SourceFolderId
			AND NOT EXISTS (SELECT DL.[DocumentLinkId] FROM [dbo].[DocumentLinks] DL WHERE DL.[DocumentId] = D.[DocumentId])
		
		UNION ALL
		
		SELECT
			 D.[ParentId]
			,D.[DocumentId]
			,D.[DocumentGuid]
			,[DocumentsToCopy].[Level] + 1 AS [Level]
			,D.[OwnerId]
		FROM [dbo].[Documents] D
			INNER JOIN [DocumentsToCopy] ON [DocumentsToCopy].[DocumentId] = D.[ParentId]
			INNER JOIN [dbo].[Users] U ON U.[UserId] = D.[OwnerId]
			INNER JOIN [dbo].[TenderCompanies] TC ON TC.[TenderId] = @SourceTenderId AND TC.[CompanyId] = U.[CompanyId] AND TC.[RoleId] = 2/*Buyer*/
		WHERE NOT EXISTS (SELECT DL.[DocumentLinkId] FROM [dbo].[DocumentLinks] DL WHERE DL.[DocumentId] = D.[DocumentId])
	)

	INSERT INTO @oldDocuments
	(
		 parentId
		,docId
		,docGuid
		,[Level]
			,[OwnerId]
	)
	SELECT
			 DTC.[ParentId]
			,DTC.[DocumentId]
			,DTC.[DocumentGuid]
			,DTC.[Level]
			,DTC.[OwnerId]
	FROM [DocumentsToCopy] DTC

	SELECt * FROM @oldDocuments


	--Insert new documents
	DECLARE @CurrentDocId INT
	SET @CurrentDocId = (SELECT TOP 1 docId from @oldDocuments order by [Level] ASC)
		DECLARE @ParentId INT = NULL
	DECLARE @CurrentParentId INT = NULL	
		WHILE @CurrentDocId IS NOT NULL
		BEGIN
			SET @CurrentParentId = (SELECT parentId FROM @oldDocuments WHERE docId = @CurrentDocId)
	 
			  IF @CurrentParentId IS NOT NULL
			  BEGIN
				SET @ParentId = (select newDocId from @oldDocuments where docId = @CurrentParentId)
			  END
			  IF (SELECT [Level] from @oldDocuments WHERE docId = @CurrentDocId) = 1
			  BEGIN
				SET @ParentId = @DestionationRootFolderId
			  END
	
			DECLARE @NewDocumentId INT
  			DECLARE @NewDocumentGuid UNIQUEIDENTIFIER = NEWID()
				DECLARE @SourceBlobGuid UNIQUEIDENTIFIER;
			DECLARE @NewBlobGuid UNIQUEIDENTIFIER;
	
			SET @NewBlobGuid = NULL;
	
			--
			-- Get Document BlobGuid
			--
			SELECT @SourceBlobGuid = [BlobGuid]
			FROM [dbo].[Documents]
			WHERE ([DocumentId] = @CurrentDocId);

			--
			-- If Document has connectected Blob then copy it with new GUID
			--
			IF NOT @SourceBlobGuid IS NULL
			BEGIN
				SET @NewBlobGuid = NEWID();
	
				INSERT INTO [dbo].[BlobsSyn] 
					(BlobGuid, Data)
					SELECT @NewBlobGuid as BlobGuid, Data
					FROM [dbo].[BlobsSyn]
					WHERE BlobGuid = @SourceBlobGuid;
			END

			INSERT INTO [dbo].[Documents]
				(
				 [DocumentGuid]
				,[BlobGuid]
				,[Name]
				,[Extension]
				,[Description]
				,[Size]
				,[ParentId]
				,[ShortcutTargetId]
				,[IsFolder]
				,[OwnerId]
				,[LastModifiedDate]
				,[CreationDate]
				,[PreviousVersionId]
				,[IsPreviousVersion]
				,[IsShared]
				,[IsHidden]
				,[VisibleToBuyers]
				,[VisibleToSuppliers] 
				 )
			SELECT
				 @NewDocumentGuid
				,@NewBlobGuid
				,[Name]
				,[Extension]
				,[Description]
				,[Size]
				,@ParentId
				,[ShortcutTargetId]
				,[IsFolder]
				,[OwnerId]
				,GETUTCDATE()	
				,GETUTCDATE()	
				,NULL			
				,0	
				,[IsShared]
				,[IsHidden]
				,[VisibleToBuyers]
				,[VisibleToSuppliers]
			FROM [dbo].[Documents]
			WHERE ([DocumentId] = @CurrentDocId)

	
			SELECT @NewDocumentId = SCOPE_IDENTITY();
	
			UPDATE @oldDocuments
			SET newDocId = @NewDocumentId
			WHERE docId = @CurrentDocId
	
			SET @CurrentDocId = NULL
			SELECT TOP 1 @CurrentDocId = [docId] FROM @oldDocuments WHERE [newDocId] IS NULL order by [Level] ASC
			END


END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  

    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO