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.
- [Instructor] Our finished process should look like this. We get the files from the source folder and we loop through the files performing the following steps. We determine which customer the file is from based on the file name, here. And then we set the appropriate parameters for the bulk insert procedure. So for our World Bank customer, it's tab-separated. The row terminator is char 10, it's a Unicode file and we SET the bank for our other customers, it's comma-separated. Character 10 linefeed row terminator, it's an NT file, so it's just char. We SET the total file path, the full file path, and source directory and file name. We execute the import into the appropriate staging table. This is successful, so it returns a one, then we check the integrity, so we have separate procedures for the different file formats, and then we import that into our generic staging table. Also for successful, we perform the schedule upload where we create and update the schedule records, if that is successful, then we check for errors. We update the production table. If it's the World Bank, then we create the vault orders, and also, which in turn sends out emails notifying of vault orders, and then for all customers, we send the email confirmation. If any file, if any errors have been logged for that file, we also send those, and finally, we move the file from the source directory to the archive directory and then rinse and repeat, and we will keep processing files until there are no more files in our files data set.
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.