Contents
Import and Validate CSV file Data into SQL Server
In this course, we cover importing and validating text file data, data conversion, error logging and notification, and financial transaction creation. You’ll see how to create an SQL Server script that is robust, error-tolerant, and can be run as a scheduled unattended job. On top of demonstrating how to get text data into database tables using only SQL, this course highlights what kind of issues may be faced when integrating with third-party systems that have less-than-ideal data structures.
Learning Objectives
- Learn how to use SQL to import CSV file data
- Learn how to build robust and error-tolerant SQL code
- Learn how to send reports and error notifications
Intended Audience
- DBAs
- System analysts
- Programmers
- Anyone wanting to learn how to use SQL to solve common data import and transformation scenarios
Prerequisites
This course assumes students have basic SQL skills. Students should be familiar with:
- SLEECT, INSERT, UPDATE, and DELETE statements
- SELECT INTO temporary tables
- WHERE NOT EXIST clauses
- Stored procedures
- Variables
Hi, and welcome to this Import and validate CSV file data into SQL Server course. My name is Hallam Webber, and I will be your instructor for this course. This is an intermediate-level course suited to DBAs, system analysts, programmers, or anyone wanting to learn how to use SQL to solve common data import and transformation scenarios. It will help to have a basic understanding of Structured Query Language syntax and inserting, updating, and selecting data. You should also be familiar with database concepts like tables, data types, stored procedures, and variables.
This course is a little unusual in that we’ll be going through an example of a real-world business scenario involving ATM, that is automatic teller machine, replenishment order processing. Other courses and examples typically use curated datasets designed with the learning objective in mind. In this course, we’ll be using data structures from third-party systems that are far from best of breed. These databases are good examples of what you may deal with when integrating with older systems.
In the process of importing the data, we will look at executing OS commands from SQL Server, using the bulk insert command, SQL cursors and while-loops, dynamic SQL, error handling, transactions, scalar and table functions, user-defined table types, and sending emails from SQL Server, with a liberal sprinkling of built-in SQL Server functions. SQL Server Management Studio is used for the demonstrations.
We welcome all comments and feedback. So please feel free to reach out to us at support@cloudacademy.com with any questions or comments.
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.