Move Child Records to New Parent and Update key 1

[dfrazell]

I have a customer manager application I’ve built that I need to write a utility to move customer contacts from one customer to another. For example, if I have customer ABC and I want to move the contacts to customer XYZ.

[b]Customer ABC[/b]
CRMID   ContactID    Name   Phone
1111            1    Bill    555-1234
1111            2    Steve   555-3600
1111            3    Ted     555-1400

[b]Customer XYZ[/b]
CRMID   ContactID    Name   Phone
2222            1    Jane    555-4321
2222            2    Mary    555-8600
2222            3    Sue     555-9400
2222            4    Ann     555-6789

The result would be:

[b]Customer XYZ[/b]
CRMID   ContactID    Name   Phone
2222            1    Jane    555-4321
2222            2    Mary    555-8600
2222            3    Sue     555-9400
2222            4    Ann     555-6789
2222            5    Bill    555-1234
2222            6    Steve   555-3600
2222            7    Ted     555-1400

I have a store procedure I would like to use that adds contacts to a customer. It increments the ContactID and performs some other record formatting tasks.

In a new stored procedure how can I move contact records from one specified customer to another by calling my stored procedure? My stored procedure has a parm list that accepts one record at a time.

Thanks for any help or ideas!

 

[bborissov]

DECLARE @MaxId INT
DECLARE @MinId INT
SELECT @MaxId = MAX(ContactId)
       FROM Contacts WHERE CRMID = 2222
SELECT @MinId = MIN(ContactId)
       FROM Contacts WHERE CRMID = 1111

UPDATE Contacts SET CRMID = 2222,
                    ContactId = (ContactId - @MinId + 1)+@MaxId
       WHERE CRMID = 1111

NOT TESTED!!!!!
Make a very good backup first.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[hmckillop]

One thing I would question is your design.

Current Design - tables

Customer
--------
CRMID
CRMName
other fields

Contact (or Could be defined as customerContac
--------
CRMID
COntactID
COntactName
ContactPhoneNo


This seems to me like a many to many relationship and probably should be

Customer
--------
CRMID
CRMName


Contact
-------
ContactID
ContactName
ContactPhoneNo


CustomerContact
-------
CRMID
ContactID


The rationale I base this on is

For example, if I have customer ABC and I want to move the contacts to customer XYZ

Does this then mean customer ABC no longer exists or doesnt have any contacts.
Why would you move the contacts from any customer.

Apologies i may have completely missed what you are asking to do but does seem like there is something just not quite right here.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[dfrazell]

I haven't tried bborissov's solution yet but it looks like it will work. I love it when I've made a problem too hard and someone comes back with a simple solution. Thanks.

To answer hmckillop, it is a one to many relationship. The reason we want to move the contacts is because customers have been duplicated by the account managers and this utility will be used to merge them back into a single customer.

Thanks.