CREATE PROCEDURE [dbo].[CopyCustomerAttachments] @FromCustomerNumber INT, @ToCustomerNumber INT AS
SET NOCOUNT ON

/*
	Created by Villy Damsbo on February 18īth 2010
	Used to copy Attachments from one customer to another. 
        Used by [CopyCustomerProfile].
	Main structure taken from procedure [CopyAttachments].
	[[CopyCustomerAttachments]] has a simpler way of copying Attachments 
	for SQL Server 2005 and newer versions than [CopyAttachments].
*/

DECLARE @Date SMALLDATETIME
DECLARE @FileGroup SMALLINT
DECLARE @DateStamp SMALLDATETIME
DECLARE @Batch BIT
DECLARE @AddressTypeNumber INT
DECLARE @Name VARCHAR(255)
DECLARE @TemplateNumber SMALLINT        
DECLARE @AttachmentNumber INT
DECLARE @AttachmentNumberNew INT
DECLARE @ptrval VARBINARY(16) 
DECLARE @StartIndex INT 
DECLARE @RequestDataBodyLength INT 
DECLARE @RequestDataBody VARCHAR(8000) 
DECLARE @productversion SQL_VARIANT
DECLARE @NewestAttachment INT

-- House keeping. Deleting existing attachments on destination before copying new ones
DECLARE DeleteCur Cursor FOR
  SELECT AttachmentNumber 
  FROM CustomerAttachmentXTab WITH (NOLOCK) 
  WHERE CustomerNumber = @ToCustomerNumber
   
/* Alternative, goes deeper:
  SELECT Attachment.AttachmentNumber 
  FROM Attachment (NOLOCK) 
  WHERE Attachment.Name IN (SELECT Attachment.Name 
                            FROM Attachment (NOLOCK) JOIN CustomerAttachmentXTab (NOLOCK) ON 
                              Attachment.AttachmentNumber = CustomerAttachmentXTab.AttachmentNumber
                            WHERE CustomerAttachmentXTab.CustomerNumber = @ToCustomerNumber)  
*/                        
  
OPEN DeleteCur  
FETCH NEXT FROM DeleteCur INTO @AttachmentNumber
WHILE @@Fetch_status = 0
BEGIN
  DELETE FROM CustomerAttachmentXTab 
  WHERE AttachmentNumber = @AttachmentNumber
  
  DELETE FROM ReservationAttachmentXTab 
  WHERE AttachmentNumber = @AttachmentNumber
  
  DELETE FROM Attachment 
  WHERE AttachmentNumber = @AttachmentNumber 
  
  FETCH NEXT FROM DeleteCur INTO @AttachmentNumber
END

CLOSE DeleteCur
DEALLOCATE DeleteCur

-- Determination of MS SQL version
DECLARE VersionCursor CURSOR FOR
SELECT SERVERPROPERTY('productversion') 
  
OPEN VersionCursor
FETCH NEXT FROM VersionCursor INTO @productversion
CLOSE VersionCursor
DEALLOCATE VersionCursor

-- Copying

/* MS SQL SERVER 2005 and later versions */

IF @productversion > '9' OR @productversion < '2'
BEGIN

  -- Determination of status for attachmentnumber 
  DECLARE GeneratorCursor CURSOR FOR
  SELECT MAX(AttachmentNumber) 
  FROM Attachment
  
  OPEN GeneratorCursor
  FETCH NEXT FROM GeneratorCursor INTO @NewestAttachment
  CLOSE GeneratorCursor
  DEALLOCATE GeneratorCursor
  
  INSERT INTO Attachment (FileGroup, DateStamp, Batch, AddressTypeNumber, Name, TemplateNumber, Blob) 
  SELECT 
    Attachment.FileGroup, 
    Attachment.DateStamp,
    Attachment.Batch, 
    Attachment.AddressTypeNumber,
    Attachment.Name,
    Attachment.TemplateNumber, 
    Attachment.Blob
  FROM Attachment WITH (NOLOCK)
    JOIN CustomerAttachmentXTab WITH (NOLOCK) ON Attachment.AttachmentNumber = CustomerAttachmentXTab.AttachmentNumber
  WHERE CustomerAttachmentXTab.CustomerNumber = @FromCustomerNumber
  
  INSERT INTO CustomerAttachmentXTab (CustomerNumber, AttachmentNumber) 
  SELECT 
    @ToCustomerNumber, 
    Attachment.AttachmentNumber
  FROM Attachment WITH (NOLOCK)
  WHERE Attachment.AttachmentNumber > @NewestAttachment 	  

END
ELSE
/* MS SQL SERVER 2000 and earlier versions */
BEGIN

  DECLARE CopyCur Cursor Forward_Only FOR
    SELECT
      Attachment.FileGroup, 
	  Attachment.DateStamp,
	  Attachment.Batch,
	  Attachment.AddressTypeNumber,
	  Attachment.Name,
	  Attachment.AttachmentNumber,
	  Attachment.TemplateNumber
    FROM Attachment WITH (NOLOCK)
      JOIN CustomerAttachmentXTab WITH (NOLOCK) ON Attachment.AttachmentNumber = CustomerAttachmentXTab.AttachmentNumber
    WHERE CustomerAttachmentXTab.CustomerNumber = @FromCustomerNumber

  OPEN CopyCur
  FETCH NEXT FROM CopyCur INTO @FileGroup, @DateStamp, @Batch, @AddressTypeNumber, @Name, @AttachmentNumber, @TemplateNumber

  WHILE (@@Fetch_status = 0)
  BEGIN

    INSERT INTO Attachment (FileGroup, DateStamp, Batch, AddressTypeNumber, Name, TemplateNumber) 
	  VALUES (@FileGroup, @DateStamp, @Batch, @AddressTypeNumber, @Name, @TemplateNumber)
    
    SELECT @AttachmentNumberNew = @@identity   
    
    INSERT INTO CustomerAttachmentXTab (CustomerNumber, AttachmentNumber) 
	  VALUES (@ToCustomerNumber, @AttachmentNumberNew) 

    --clear any existing data in destination blob field
    UPDATE Attachment 
    SET Blob = NULL 
    WHERE AttachmentNumber = @AttachmentNumberNew 

    SET @StartIndex = 0 

    --get length of data to copy 
    SELECT @RequestDataBodyLength = DATALENGTH(Blob)
    FROM Attachment WITH (nolock)
    WHERE AttachmentNumber = @AttachmentNumber 

    --get text pointer to destination blob field
    SELECT @ptrval = TEXTPTR(Blob) 
    FROM Attachment WITH (nolock)
    WHERE AttachmentNumber = @AttachmentNumberNew  

    --copy data in chunks of 8000 from source to destination
    WHILE @StartIndex < @RequestDataBodyLength
    BEGIN 
	SELECT @RequestDataBody = SUBSTRING(Blob, @StartIndex + 1 , 8000) 
	FROM Attachment WITH (nolock)
	WHERE AttachmentNumber = @AttachmentNumber
	
	UPDATETEXT Attachment.Blob @ptrval @StartIndex 0 @RequestDataBody
	SET @StartIndex = @StartIndex + DATALENGTH(@RequestDataBody) 
    END
   
    FETCH NEXT FROM CopyCur INTO @FileGroup, @DateStamp, @Batch, @AddressTypeNumber, @Name, @AttachmentNumber, @TemplateNumber
    
  END;

  CLOSE CopyCur
  DEALLOCATE CopyCur

END;



