SQL Server: Transact-SQL Basic Data Modification

Learn how to construct INSERT, UPDATE, and DELETE statements to modify data in SQL Server databases, applicable for developers from SQL Server 2005 onwards
Course info
Rating
(383)
Level
Beginner
Updated
Sep 18, 2012
Duration
2h 32m
Table of contents
Introduction
Setting Up a Transact-SQL Learning Environment
Exploring Table Metadata
Inserting Data
Updating Data
Deleting Data
Beyond the Basics
Description
Course info
Rating
(383)
Level
Beginner
Updated
Sep 18, 2012
Duration
2h 32m
Description

If you need to modify data in a SQL Server database, then you need to know how to use the INSERT, UPDATE and DELETE statements. This course starts by explaining how to find information about the tables and columns you want to modify. It then explains the INSERT, UPDATE, and DELETE statements in detail along with various methods for limiting the data being modified. Finally it moves beyond the basic modification statements to more advanced topics like the MERGE statement, error handling and more. More then thirty five demos help to give you a thorough understanding of how to perform these essential operations, all using a freely-available demo environment that you're shown how to set up and configure. This course is perfect for developers who need to modify data in SQL Server databases, from complete beginners through to more experienced developers who can use some of the modules as reference material. The information in the course applies to all versions from SQL Server 2005 onwards.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Transact-SQL basic data modification course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. ( Silence ) If you're creating or supporting an application that connects to SQL Server, then that application will be using Transact-SQL either explicitly by a hand coded Transact-SQL statements or implicitly through automatic code generation or application programming interfaces. For those of you who are brand new to the SQL language itself, SQL stands for structured query language and it's the language one would use to manage and retrieve data from a relational database management system. Transact-SQL or T-SQL for short is Microsoft's implementation of the anti-standard SQL query language. There are a few different categories of SQL statements including DDL which is data definition language, DCL which is data control language, and DML which is data manipulation language. And this course focuses on DML and specifically the insert, update, delete, and merge data modification statements. This course is recommended for those of you who are new to Transact-SQL or have minimal experience and specifically need to learn how to modify data in a SQL Server database. And throughout this course, I'll be demonstrating the data modification concepts using the SQL Server 2012 version of the database engine but keep in mind that the majority of what I'll be covering applies to previous versions of SQL Server as well. For example, 2005, 2008, and 2008 R2 and when something is only available in SQL Server 2012 and beyond, I'll explicitly call it out.

Setting Up a Transact-SQL Learning Environment
Joe Sack: Hi, this is Joe Sack, from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Basic Data Modification course, and you're watching Module 2, where you'll learn how to set up your own Transact-SQL learning environment. This module will cover how to set up your own learning environment so that you can follow along with the presented concepts and demonstrations. I definitely recommend that you practice the concepts and techniques covered in this course as this will be the best way for you to reinforce you new skills. In this module, I'll walk through how to install SQL Server 2012 Express Edition, SQL Server Data Tools and the Adventureworks for SQL Server 2012 sample database. At the end of the module, I'll demonstrate executing a basic data modification statement, both in SQL Server Management Studio and in SQL Server Data Tools.

Exploring Table Metadata
Hi, this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Basic Data Modification course, and you're watching module three, where you'll learn how to explore table metadata prior to performing data modification operations. Prior to making data modifications to table data, you need to understand which columns are used in the table, their associated data types, defaults, properties, constraints associated with the data, and also the relationship of that table to other tables and objects. In this module, I'll walk through how to use stored procedures and system catalog views in order to understand a table's metadata. We'll cover how to determine relationships between tables, which is important particularly in cases where a specific order of data modification operations is required. And you'll be introduced to methods for identifying object associations beyond just primary key and foreign key relationships. For example, understanding which triggers are associated with your table, and understanding what side effect operations will occur based on your own data modifications.

Inserting Data
Hi, this is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Basic Data Modification course, and you're watching Module 4 where I'll cover the fundamentals of the insert statement. In this module I'll provide a quick tutorial on how to backup and also restore your demonstration database before making the changes shown throughout this course. And next, I'll cover the basic insert syntax, how to perform single-statement multiple row inserts, how to handle default values, and how to populate data from derived tables and also stored procedures.

Updating Data
Hi. This is Joe Sack from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Basic Data Modification course, and you're watching Module 5 where I'll cover the fundamentals of the update statement. In this module, I'll cover the basics of the UPDATE statement, how to use UPDATE with the "from" clause, how to handle default column values, how to use compound assignment operators, and also how to update in conjunction with sub-queries, and then we'll finish off with how to update data using the view.

Deleting Data
Joe Sack: Hi. This is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Basic Data Modification course and you're Module 6 where I'll cover the fundamentals of the DELETE statement. In this module I'll cover the basics of the DELETE statement, how to use the WHERE clause with the DELETE and how to use the FROM clause with DELETE and also give you and understanding of the differences between a DELETE statement and a TRUNCATETABLE operation. ( Silence )

Beyond the Basics
( Silence ) Hi, this is Joe Sack from sqlskills. com. And I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Basic Data Modification course. And you're watching Module 7 where we'll go beyond the basics for data-modification operations. In this module we'll cover the merge statement. How to perform data modifications in conjunction with Common Table Expressions. How to update large-value data types. Understanding minimal-logging operations. Learn about sequence objects as an alternative to identity property columns. Use the output clause to return rows affected by data-modification operations. Learn how to batch modifications into smaller chunks using TOP. Learn about the value of using explicit transactions for data modifications. And lastly, how to apply error handling to your scripts. So that you can catch data-modification errors and handle them more gracefully. ( Pause )