Mail Merge in SQL

USE AdventureWorks
GO

-- My customer table
SELECT * FROM Customers
GO

-- create a mail merge work table
CREATE TABLE #mail_merge(
   customer_id        INT
  ,name               VARCHAR(256)
  ,address            VARCHAR(256)
  ,city               VARCHAR(256)
  ,zip                VARCHAR(10)
  ,email_address      VARCHAR(256)
  ,email_body         VARCHAR(MAX)
)

-- populate #mail_merge with Customers 
INSERT #mail_merge(
   customer_id
  ,name      
  ,address   
  ,city      
  ,zip  
  ,email_address     
)
SELECT CustomerID
      ,FirstName + ' ' + LastName
      ,AddressLine1
      ,City
      ,PostalCode
      ,EmailAddress
  FROM Customers

-- Read content of letter and assign it to every row
UPDATE #mail_merge
   SET email_body = (SELECT *
                       FROM OPENROWSET(BULK 'C:\Adv_Letter.htm', SINGLE_CLOB) AS letter_template)

-- Update the letter with customer attributes
UPDATE #mail_merge
   SET email_body =  REPLACE(email_body,'Customer_Name', name)

UPDATE #mail_merge
   SET email_body =  REPLACE(email_body,'Customer_Address', address)

UPDATE #mail_merge
   SET email_body =  REPLACE(email_body,'Customer_City', city)

UPDATE #mail_merge
   SET email_body =  REPLACE(email_body,'Customer_ZIP', zip)

-- Declare a cursor for each row in #mail_merge
-- send an email for each row in #mail_merge      
DECLARE @email_address VARCHAR(256)
DECLARE @email_body    NVARCHAR(MAX)
DECLARE mail_merge     CURSOR FOR SELECT email_address, email_body FROM #mail_merge

OPEN mail_merge

FETCH NEXT FROM mail_merge INTO @email_address, @email_body
WHILE @@FETCH_STATUS = 0
BEGIN

   EXECUTE msdb..sp_send_dbmail
	       @profile_name  = 'DemoProfile'      
          ,@recipients    =  @email_address
	      ,@body          =  @email_body
	      ,@subject       = 'Adventure Open House Announcement'
          ,@body_format   = 'HTML'
	
	FETCH NEXT FROM mail_merge INTO @email_address, @email_body

END

-- clean up; close cursor and drop work table
CLOSE mail_merge
DEALLOCATE mail_merge
DROP TABLE #mail_merge
GO