Mail Merge in SQL

Mail Merge sample


This code demonstrates how to use Mail merger 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


Comments

Author: Phagu Mahato13 Oct 2013 Member Level: Gold   Points : 5

Thank for useful resource in SQL.The following code behind is used for the underlying merge process in SQL Server at C-Sharp:

foreach (TXTextControl.AppField field in txt.AppFields)
{
try
{
if (field.TypeName != "MERGEYOURFIELD")
return;


TXTextControl.DocumentServer.Fields.MergeField mf = new TXTextControl.DocumentServer.Fields.MergeField(field);

if (this.ProductsDataSet.Products.Columns.Contains(mf.Name) == false)
continue;


field.Text = this.ProductsDataSet.Products.Rows[
dataGridView1.SelectedRows[0].Index][mf.Name].ToString();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: