March 21, 2012 at 2:38 pm
I have a bit of a problem with getting some data updated properly but am unsure how to proceed.
I'm using a merge statement to update a table everyday with the data retrieved from a stored procedure. The stored procedure runs and gathers all the changes users have made to client file data that day and updates the table with those changes. Due to the sometime-inaccurate nature of users, I had to create an application for the users to use when they performed something incorrectly in updating the client files, which couldn't be fixed from within the program they originally perform the actions in. The application simply enters the correct/updated info directly into the table to correct the mistake that was originally made. However, the merge statement continues to run each night to update all data in the table and then overwrites the values they entered via the application. I still need the merge to happen each night, but how can i get around it overwriting these values each night? Do I need to check DDL updates to the table or something?
Anyone have any ideas?
Thanks
March 21, 2012 at 2:50 pm
Your criteria for which rows to update in your merge is incomplete. Since you forgot to include the DDL for your tables, sample data, expected results, and your current MERGE statement, it's going to be very difficult for anyone to help you determine how to correctly specify the records to update.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2012 at 2:56 pm
As far as the data goes, it gets complicated.
I just wondered how someone might approach a situation where a merge statement updates all rows of data in a table each night, but to make it so that it does not overwrite those fields that had been "manually" updated via the outside application. I thought maybe using a trigger for DDL updates to the table to identify the changes that are taking place which are not done by the nightly update and...well...do something with that information.
In a general sense, does anyone have ideas of how to capture data being updated in a table by two different means, so that one update doesn't overwrite the other? Is there a way to flag the values that the application changes so that I can note those flags and have the merge exclude those values or something? (just thinking off the top of my head here...)
March 21, 2012 at 3:15 pm
Assuming that there is some date changed associated with each of the changes, use that to determine which change is more recent.
Option 2 is to update the source of the overwrites instead of the destination of the overwrites.
Option 3 is to remove any changes that have already been applied.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2012 at 3:38 pm
I think I'll have to resort to #2.
It seems to theoretically be the simplest way to solve the situation, even though I was hoping to avoid going that route.
Thank you for your input, Drew.
Much appreciated
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply