1) Tables
Users Table with all details UserId, UserName and PhoneNumber
UserPhoneHistory Table with UserId, PhoneNumbers 

Users 
UserId, UserName, PhoneNumber
1        chandu    6768974

UserPhoneHistory 
UserId, PhoneNumbers 
1        3234712
1        4542352

2) Whenever user wants to update Phone number then insert that deleted record into USerPhoneHistory table ( either use TRIGGER or Stored Procedure )

Trigger for UPDATE

CREATE TRIGGER Tr_UserPhoneUpdate 
ON Users 
FOR UPDATE
AS 
IF ( UPDATE (PhoneNumber))
BEGIN
   INSERT UserPhoneHistory (UserId, PhoneNumbers )
   SELECT d.UserId, d.PhoneNumber
   FROM deleted d 
   JOIN inserted i ON i.UserId = d.UserId
   WHERE i.PhoneNumber NOT IN (SELECT u.PhoneNumbers FROM UserPhoneHistory u WHERE u.UserId = d.UserId )
END

NOTE: Also check for phone Number duplication 

quote:

---

Originally posted by Ganesan

Thank you Chandu for your quick reply.
I'm working in Industrial Automation Domain, So, it's somewhat tough to understood, I have an idea, but i don't know how to make as SQL Query.

I have 3 column as i said earlier, first time execution needs to execute in first column and so on, IS that possible, because that is easy to me to implement in my project.

I seen a command called "coalesce", this can tell us which column is free? from here can we do something on which column the current data needs to update?

Thanks
Ganesan

---

CREATE  TRIGGER TriggerName
ON Users
FOR UPDATE
AS
BEGIN
INSERT UserPhoneHistory
SELECT d.*
FROM INSERTED i
JOIN DELETED d
ON d.UserID = i.USERID
WHERE i.PHONENO <> d.PHONENO
END