The Power of Database Views
Nov
18
Written by:
11/18/2009 3:40 PM
Database views, as I think of them, are mini-queries of your data. I was working with an SSIS package this week where my Main table had to be updated with case totals for specific MSDRGs. The MSDRG was the key field in Main and 4 other tables had to feed it, only if there was a match on MSDRG. The other tables had numerous entries that were not matches, but the project required a match.
I initially tried to design my SSIS package to Union All, Merge or Merge Join the 5 inputs (Main tables plus 4 sub tables). However, this did not work. So, I turned to my friend, SQL Server Management Studio, and created views - 1 for the file containing the MSDRGs I was interested in AKA "The Boss Table" and 4 other views from the feeder tables. I joined the Boss Table and feeder tables on MSDRG with a left outer join so all records in Boss Table would be available. This worked perfectly!
Lessons Learned:
- Understand limitations of your tools. I tried to do too much with SSIS, and found SSMS to handle the task better.
- It is possible to create a database view from other views - new to me :)
- The SQL Server Import/Export Wizard is your friend when you need to offload data to Excel.
In observance of Thanksgiving, I will not post a blog entry next week. I wish all of you a blessed and happy Thanksgiving!