October 19, 2011 at 9:28 am
Good Morning,
We're working on automating a system where users will upload data from a spreadsheet into a single table ("Employee_Demographics") that we'll use to keep record of what was uploaded should there ever be any discrepancies in the data. Once inserted into "Employee_Demographics", the record(s) will update an existing record or be inserted into the main table ("Employee_File"). My question pertains to the update portion of the process as for each column in the "Employee_File", if the corresponding "Employee_Demographics" column value is null we do not want to update the "Employee_File" with this value as some of our clients don't follow our specifications and only fill out the spreadsheet with values that have changed. Would I need to write a separate UPDATE statement for each column in the tables using the following syntax or is there better solution that would prevent the need for an UPDATE statement with each column in the table.
FYI - Primary key on Employee_File is (company_id, employee_id)
update employee_file
set first_name = employee_demographics.first_name
from employee_file f
join employee_demographics d
on (f.company_id = d.company_id
and f.employee_id = d.employee_id)
where (f.first_name is null and d.first_name is not null)
or (f.first_name is not null and d.first_name is not null and (f.first_name != d.first_name))
The SQL Server instance this is currently being run on is currently SQL Server 2000. However, it will be moved to SQL Server 2008 soon. Thank you very much for any help provided.
October 19, 2011 at 9:53 am
If I understand correctly, something like this should work:
update employee_file
set first_name = isnull(d.first_name, f.first_name),
last_name = ISNULL(d.last_name, f.last_name),
[rest of your columns...]
from employee_file f
join employee_demographics d
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2011 at 9:56 am
To prepare for moving to 2008 may I suggest you read up on the MERGE statement to determine if it will satisfy your requiements.
October 19, 2011 at 10:23 am
Thank you for the replies.
Sean I think that should work as well. I knew there was a better way but just couldn't think of it 🙂
I actually just came across a few articles the other day on the MERGE statement I planned on taking a look at; thanks for the link, I will take a look at it as well.
Have a great day!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply