SQLTeam.com | Weblogs | Forums

Store procedure to copy data


#1

I have two tables. Customer_Raw and Customer with same fields susch as
CustID
Name
Contact
Address
City
State
Zip
Lat
Long
Lat and Longitude is always 1 in Customer_RAW table, but it has proper number in Customer table
I need to copy data from Customer_Raw to Customer table Customer_ID is the primary key
If record does not exist just append the whole line.
If records exists i need to update Name, Contact, Address, City, State, Zip .
Also if I see that the Address field is different than existing one I need to update the existing Lat and Long to 1.
If no change in Address field I would have to keep the existing Lat and Long record.
Can I get some help with this complicated update?
Thanks


#2
update c
    SET c.name = r.name,
             c.Contact = r.Contact
             ... etc...
  
from customer c
join customer_raw r
  on c.Customer_Id = r.Customer_Id

Plus:

Insert into Customer(Customer_Id, Name, Contact, ...)
Select Customer_Id, Name, Contact, ...
FROM customer_raw r
where not exists 
(
    select 1 from Customer c where c.Customer_Id = r.Customer_ID 
)

#3

This is great thanks for help. I will work on the code shortly. I only have one more problem. The first part of the code updates the record if exist it works fine I think however I am required to do one more thing.
If I find that the address field in Customer table is different from Customer Raw table then I need to update the lat and long. If not different then no update. I probably need some if statement added to the first part?


#4

right. Add where clauses as needed