Splitting Columns and creating new records.

Customer has entered in some contacts together “Sara and Jack Smith” BUT
They want them as separate contacts  “Sara Smith” and “Jack Smith”
The only problem is that it takes pieces of peoples names like brANDon or companies like James & james LLC.
Alos, I am getting three records instead of two from the following script.  What am I doing wrong?  
Thanks in advance!
SELECT  FirstName, * FROM  ContactName WHERE  FirstName LIKE '%&%'   

BEGIN TRAN
SELECT cn.* 
INTO #tempcontact 
FROM contactname cn 
	JOIN entity e ON cn.uniqentity = e.uniqentity
					and e.entkey = 'CUST'
WHERE firstname like '%&%'
	AND rtrim(firstname) NOT LIKE '%&'

INSERT INTO contactname (UniqFixedContactName, UniqEntity, FirstName, LastName, UniqContactAddressMain, UniqContactNumberMain, InsertedByCode)
SELECT 
UniqFixedContactName = -1,
UniqEntity,
firstname = ltrim(replace(substring(firstname, charindex('&',firstname,1),30),'&','')),
LastName,
UniqContactAddressMain,
UniqContactNumberMain,
InsertedByCode = 'BBB'
FROM  #tempcontact

UPDATE contactname
SET UniqFixedContactName = UniqContactName
WHERE UniqFixedContactName = -1
AND InsertedByCode = 'BBB'

UPDATE cn
SET  FirstName = substring(FirstName, 1, charindex('&',firstname,1) - 1), UpdatedByCode = 'BBB', UpdatedDate = GETUTCDATE()
FROM contactname cn
where uniqcontactname in (select uniqcontactname from #tempcontact)

SELECT * FROM contactname WHERE  UpdatedByCode = 'BBB' OR InsertedbyCode = 'BBB'
ORDER BY uniqentity

DROP TABLE #tempcontact

ROLLBACK

Open in new window

LVL 6
dsulkarAsked:
Who is Participating?
 
David Johnson, CD, MVPOwnerCommented:
This is a time where you will have to do a select and bring up a list of possible records to change and then manually go through them to deselect unwanted or select wanted records to change.. computers are fast but not smart.
are the resultant records
Sara and Jack Smith
Sarah Smith
Jack Smith
With the original record not being deleted?
0
 
aikimarkCommented:
use " and " as the separator -- include the space characters at the front and back of the "and" literal.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.