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

--vd20170613 Renovated and simplified
    
DECLARE @AttachmentNumber INTEGER
DECLARE @NewestAttachment INTEGER

/*
DECLARE DeleteCur Cursor FOR
  SELECT AttachmentNumber 
  FROM CustomerAttachmentXTab WITH (NOLOCK) 
  WHERE 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

  UPDATE ProductCertificate 
  SET FK_CertificateAttachmentID = NULL 
  WHERE FK_CertificateAttachmentID = @AttachmentNumber
  
  DELETE FROM Attachment 
  WHERE AttachmentNumber = @AttachmentNumber 
  
  FETCH NEXT FROM DeleteCur INTO @AttachmentNumber
END
CLOSE DeleteCur
DEALLOCATE DeleteCur

The housekeeping above has been removed by appointment with Robert Madsen
*/

DECLARE GeneratorCursor CURSOR FOR
SELECT MAX (AttachmentNumber) 
FROM Attachment WITH (NOLOCK)
  
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  



