Contact Name - Need to convert into an Update or maybe Merge

[sparkbyte]

I found a SQL script the get me 99% of the way to what i need, but I am having problems converting it into either an Update or a Merge Into.

Here is the Code.

/*create table #temp 
( 
    FULLNAME        VARCHAR(100), 
    ID              INT 
) 
  
INSERT INTO #TEMP VALUES ('TUCKER, KEVIN G', 1) 
INSERT INTO #TEMP VALUES ('SCOTT, JOHN', 2) 
INSERT INTO #TEMP VALUES ('ERIC, T W', 3) 
INSERT INTO #TEMP VALUES ('MUNICH, SMITH D', 4) 
INSERT INTO #TEMP VALUES ('LYOD SR, CLIVE G', 5) 
INSERT INTO #TEMP VALUES ('HANSEN JR, CHARLES S', 6) 
INSERT INTO #TEMP VALUES ('BROWN,SHERMAN', 7) 
INSERT INTO #TEMP VALUES ('ANDREWS III, CLARK A', 8) 
INSERT INTO #TEMP VALUES ('MAMMTAN, MARY LOU', 9) 
*/ 

USE eWRTs_SAVE
GO

DECLARE  @Suffixes  TABLE( 
                         Suffix VARCHAR(5) 
                          ) 
 
INSERT INTO @Suffixes 
VALUES     ('I'), 
           ('II'), 
           ('III'), 
           ('IV'), 
           ('V'), 
           ('SR'), 
           ('JR'), 
           ('1st'), 
           ('2nd'), 
           ('3rd') 

SELECT T.ContactID, 
       T.ContactName, 
       F7.*, 
       F4.[LAST Name], 
       F4.Suffix 
FROM   dbo.Contacts_Table T
       CROSS APPLY (SELECT LEFT(T.ContactName,CHARINDEX(',',T.ContactName + ',') - 1) AS cLastName,
                           LTRIM(SUBSTRING(T.ContactName,CHARINDEX(',',T.ContactName + ',') + 1, 
                                           LEN(T.ContactName))) AS cFirstName) F1 
       CROSS APPLY (SELECT LEFT(F1.cLastName,CHARINDEX(' ',F1.cLastName + ' ') - 1) AS LName,
                           SUBSTRING(F1.cLastName,CHARINDEX(' ',F1.cLastName + ' ') + 1, 
                                     LEN(F1.cLastName)) AS pSuffix) F2 
       CROSS APPLY (SELECT CASE 
                             WHEN LEN(pSuffix) > 0 
                                  AND EXISTS (SELECT 1 
                                              FROM   @Suffixes S 
                                              WHERE  S.Suffix = pSuffix) THEN 'Y' 
                             ELSE 'N' 
                           END AS SuffixExists) F3 
       CROSS APPLY (SELECT CASE 
                             WHEN F3.SuffixExists = 'Y' THEN F2.LName 
                             ELSE RTRIM(F2.LName + ' ' + F2.pSuffix) 
                           END AS [LAST Name], 
                           CASE 
                             WHEN F3.SuffixExists = 'Y' THEN F2.pSuffix 
                             ELSE '' 
                           END AS [Suffix]) F4 
       CROSS APPLY (SELECT LEFT(F1.cFirstName,CHARINDEX(' ',F1.cFirstName + ' ') - 1) AS FName,
                           SUBSTRING(F1.cFirstName,CHARINDEX(' ',F1.cFirstName + ' ') + 1, 
                                     LEN(F1.cFirstName)) AS MInitial) F5 
       CROSS APPLY (SELECT CASE 
                             WHEN LEN(MInitial) = 1 THEN 'Y' 
                             ELSE 'N' 
                           END AS MIExists) F6 
       CROSS APPLY (SELECT CASE 
                             WHEN F6.MIExists = 'Y' THEN F5.FName 
                             ELSE RTRIM(F5.FName + ' ' + F5.MInitial) 
                           END AS [FIRST Name], 
                           CASE 
                             WHEN F6.MIExists = 'Y' THEN Upper(F5.MInitial) 
                             ELSE '' 
                           END AS [Middle Initial]) F7
WHERE ContactName NOT LIKE '%@%'

Thanks

John Fuhrman

 

[TheBugSlayer]

sparkbyte,

Does your query run? I believe you need to convert your INSERT statment to

INSERT INTO @Suffixes VALUES ('I') 
INSERT INTO @Suffixes  VALUES         ('II') 
INSERT INTO @Suffixes   VALUES        ('III') 
INSERT INTO @Suffixes  VALUES         ('IV') 
INSERT INTO @Suffixes VALUES          ('V') 
INSERT INTO @Suffixes  VALUES         ('SR') 
INSERT INTO @Suffixes  VALUES         ('JR') 
INSERT INTO @Suffixes  VALUES         ('1st')
INSERT INTO @Suffixes  VALUES         ('2nd') 
INSERT INTO @Suffixes   VALUES        ('3rd')

first. If that works and you encounter errors, post them either.

Also, are you referring to the T-SQl MERGE statement?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[fredericofonseca]

TheBugSlayer - the construct sparkbyte has for the insert into @Suffixes is perfectly valid in 2008 r2 if not earlier - no need to change it.


sparkbyte - what you have there is just a select... what would you be trying to update and based on what input and match criteria?

it would help if you gave a example of what you are trying to achieve and an explanation of what it is supposed to do without all the query bits. e.g. more like pseudo code even.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886