IMPORT_TABLE (Imported from MS Excel) <-- Source table
CONTACT_TABLE: <-- Target Table
I have to match the name from the Import table with the full_name in Contact table, if it matches, i have to update address / city / state / zip information in the Contact table if and only if corresponding columns in the target table (Contact table) is empty. If the name does not match, i have to insert a new record in contact table.
Should i use SQL cursor? If so could any one provide me with a sample solution?
Thanks in Advance,
SQL 2000 on Windows 2000 Server
You need to make two passes through the data. Once to update existing records, and once to add new records.
I don't think you want to be update just parts of addresses. That is asking for hodgepodge data. Address data should be treated as a unit, so that is what the following code does:
set Address_Line1 = IMPORT_TABLE.Address,
City = IMPORT_TABLE.City,
State = IMPORT_TABLE.State,
Zip = IMPORT_TABLE.Zip
inner join IMPORT_TABLE on CONTACT_TABLE.Full_Name = IMPORT_TABLE.Name
where (CONTACT_TABLE.Address_Line1 is null and IMPORT_TABLE.Address is not null)
or (CONTACT_TABLE.City is null and IMPORT_TABLE.City is not null)
or (CONTACT_TABLE.State is null and IMPORT_TABLE.State is not null)
or (CONTACT_TABLE.Zip is null and IMPORT_TABLE.Zip is not null)
insert into CONTACT_TABLE
left outer join CONTACT_TABLE on IMPORT_TABLE.Name = CONTACT_TABLE.Full_Name
where CONTACT_TABLE.Full_Name is null
If it's not practically useful, then it's practically useless.