Help on Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help on Query

Author

Topic

x5-452
Starting Member

8 Posts

Posted - 2014-08-08 : 09:48:30

See the below tables. Table1 holds a letter template information.
Table2 stores the Customer information and tag name and values to stuff into the template. Table3 will hold the column (EmailBody column) to store the template with the tag replacement.

Table1 - Template Master
Template Code/Template Text (text)
TEMPLATE01 / Dear <Name>, <Account> is in Arrears of <Outstanding>

Table2:
Customer / Template Code / Tag Name (char(50) / Tag Value (char(100))
ABC01 / TEMPLATE01 / <Name> / John Doe
ABC01 / TEMPLATE01 / <Account> / 1001
ABC01 / TEMPLATE01 / <Outstanding> / $500.00
DEF01 / TEMPLATE01 / <Name> / Jane Doe
DEF01 / TEMPLATE01 / <Account> / 1002
DEF01 / TEMPLATE01 / <Outstanding> / $1000.00

Table3:
Customer (char(15) / Other Info / EmailBody (text)
ABC01 / "Whatever" /
DEF01 / "Extra Info" /

I've only been able to manage to replace 1 tag and can't seem to replace all tags at once. How would I go about doing this successfully?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-08 : 10:35:05

Please post:

1. CREATE TABLE statements for the tables involved
2. INSERT INTO statements to populate the tables with test data
3. Your query so far, along with the results it produces
4. The desired results

x5-452
Starting Member

8 Posts

Posted - 2014-08-08 : 11:17:51

I managed to get it working but I really don't think it's efficient.
Any pointers how to optimize my query?

CREATE TABLE [dbo].[Table1](
[TEMPLATE] [char](15) NOT NULL,
[TEMPLATETEXT] [text] NULL)

CREATE TABLE [dbo].[Table2](
[CUSTOMER] [char](15) NOT NULL,
[TEMPLATE] [char](15) NOT NULL,
[TAG] [char](50) NOT NULL,
[TAGVALUE] [char](100) NULL)

CREATE TABLE [dbo].[Table3](
[CUSTOMER] [char](15) NULL,
[OTHERINFO] [varchar](50) NOT NULL,
[EMAILBODY] [text] NOT NULL)

INSERT INTO [CFS].[dbo].[Table1]
([TEMPLATE]
,[TEMPLATETEXT])
VALUES
('TEMPLATE01'
,'Dear <Name>, <Account> is in Arrears of <Outstanding>')

INSERT INTO [CFS].[dbo].[Table2]
([CUSTOMER]
,[TEMPLATE]
,[TAG]
,[TAGVALUE])
VALUES
('ABC01'
,'TEMPLATE01'
,'<Name>'
,'John Doe')

INSERT INTO [CFS].[dbo].[Table2]
([CUSTOMER]
,[TEMPLATE]
,[TAG]
,[TAGVALUE])
VALUES
('ABC01'
,'TEMPLATE01'
,'<Account>'
,'1001')

INSERT INTO [CFS].[dbo].[Table2]
([CUSTOMER]
,[TEMPLATE]
,[TAG]
,[TAGVALUE])
VALUES
('ABC01'
,'TEMPLATE01'
,'<Outstanding>'
,'$500.00')

INSERT INTO [CFS].[dbo].[Table2]
([CUSTOMER]
,[TEMPLATE]
,[TAG]
,[TAGVALUE])
VALUES
('DEF01'
,'TEMPLATE01'
,'<Name>'
,'Jane Doe')

INSERT INTO [CFS].[dbo].[Table2]
([CUSTOMER]
,[TEMPLATE]
,[TAG]
,[TAGVALUE])
VALUES
('DEF01'
,'TEMPLATE01'
,'<Account>'
,'1002')

INSERT INTO [CFS].[dbo].[Table2]
([CUSTOMER]
,[TEMPLATE]
,[TAG]
,[TAGVALUE])
VALUES
('DEF01'
,'TEMPLATE01'
,'<Outstanding>'
,'$1000.00')

INSERT INTO [CFS].[dbo].[Table3]
([CUSTOMER]
,[OTHERINFO]
,[EMAILBODY])
VALUES
('ABC01'
,'Whatever'
,'')

INSERT INTO [CFS].[dbo].[Table3]
([CUSTOMER]
,[OTHERINFO]
,[EMAILBODY])
VALUES
('DEF01'
,'Other Info'
,'')

Query so far:

-- Update Table3 with Template body
UPDATE Table3
SET EMAILBODY = C.TEMPLATETEXT
FROM Table3 A
INNER JOIN TABLE2 B ON A.CUSTOMER = B.CUSTOMER
INNER JOIN TABLE1 C ON B.TEMPLATE = C.TEMPLATE

--Loop through until no more tags found
WHILE @@ROWCOUNT > 0
UPDATE TABLE3
SET EMAILBODY = cast(replace(cast(A.EMAILBODY as nvarchar(max)),LTRIM(RTRIM(B.TAG)),LTRIM(RTRIM(B.TAGVALUE))) as ntext)
FROM TABLE3 A
INNER JOIN TABLE2 B ON A.CUSTOMER = B.CUSTOMER
INNER JOIN TABLE1 C ON B.TEMPLATE = C.TEMPLATE
WHERE A.EMAILBODY LIKE '%' + LTRIM(RTRIM(B.TAG)) + '%'

Results:
CUSTOMER OTHERINFO EMAILBODY
ABC01 Whatever Dear John Doe, 1001 is in Arrears of $500.00
DEF01 Other Info Dear Jane Doe, 1002 is in Arrears of $1000.00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources