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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
use " and " as the separator -- include the space characters at the front and back of the "and" literal.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.