I'm new to SQL and have done some simple reports before. Now I want to do something to help with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.
To deal with this I'm currently thinking of this approach:
- Create a view pulling all of the various data together from the multiple tables.
- Create an empty 'errors' data table.
- Create an Excel file with a button to call a Check for Errors Script
Then in the the script:
- Clear the 'errors' data table
- Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes).
- After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.
Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.
Does this sound a ) doable, b) the right approach?
I've very new to SQL, so want to make sure I approach it in the best way..
Thank you for all your help!