Working with SQL
This is an intermediate level course suited to DBAs, system analysts, programmers, or anyone wanting to learn how to use SQL to solve business scenarios. It will help to have an understanding of Structured Query Language syntax and functions and regular expressions, or some experience with script-based programming languages. Our learning objectives for this course are to recognize and explain use cases where Structured Query Language, SQL, can be applied in business situations, explain and apply SQL procedures to import and parse text files, define and apply automation processes to parse and sort data to specific databases. We welcome all comments and feedback. So please feel free to reach out to us at firstname.lastname@example.org with any questions or comments.
About the Author
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.
- [Instructor] In this course we will load and parse text data from various source files into tables. The data will be manipulated and cleaned, and formed into orders to be processed. For each order file we will load the data into staging tables. Then we will validate the data and update production data. We will then create customer transactions when needed. After each file has been loaded we will send an email notifying users of the uploaded data. If there have been any exceptions or data validation errors, emails detailing those issues will be sent. Finally, the original file is archived. They key functional points of this tutorial are that this process is implemented using just SQL. It is designed to run as a scheduled job that requires no human intervention. That being the case, we will implement a robust and error tolerant design pattern. There will be data validation and error handling. Users will be notified of the process completion and exceptions by email. To implement this functionality we will make use of dynamic SQL to create reusable and flexible code, SQL Server's Bulk insert statement, SQL cursors to perform complex procedural processing, SQL scalar and table functions to group functionality and make our code clean and concise, table variables to pass complex data between functions, and setting up and using SQL Server's DB Mail.