T-SQL Data Manipulation Inserting, Updating, Deleting, and Merging in SQL Server

  • John Deardurff

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

You're watching a preview of subscription content. Log in to check access

This video course teaches how to go beyond writing SELECT statements and to manipulate data within your database tables. You’ll begin with an introduction to Data Manipulation Language (DML) that covers the INSERT, UPDATE, and DELETE statements. Watching the video, you’ll learn how to use these three statements to insert new data into your database to tables, and to update data in place without having to bring it down to the client and push it back and forth network. You’ll also learn to delete data when it’s no longer needed.

Also covered is the MERGE statement. You’ll learn how this little used but powerful statement works to make extract, transform, load (ETL) processes much more streamlined. You’ll learn to throw large numbers of rows at your database, letting the MERGE statement sort out which rows represent new data to be inserted, and which represent updates to existing data. You’ll be able to write cleaner load processes for data warehouses, data marts, and other reporting statements using your knowledge of MERGE.

You’ll also learn some fast ways to delete data from database tables. In addition to DELETE, you’ll learn about DROP and TRUNCATE. These statements provide you with options for quickly deleting data. Each has their best use, and this video helps you to make the correct choices and avoid unwanted downtime in your production database environments. Finally, you’ll learn about identity columns and sequences, using them to generate primary keys to uniquely identify new rows of data that you are storing in your database tables. Throughout this video you’ll learn the gamut of data manipulation statements that SQL Server places at your disposal, and when each is best applied.

What You Will Learn

  • Add data to your database using INSERT and MERGE statements

  • Update data in place without sending it back and forth over the network

  • Choose correctly from three possible ways of removing unwanted data

  • Streamline ETL process used to load data marts and data warehouses

  • Automatically generate primary keys to uniquely identify new records

Who This Video Is For

For business analysts, developers, and others whose career will benefit from a knowledge of the SQL language for accessing Microsoft SQL Server databases and manipulating the data they contain.

About The Author

John Deardurff

John Deardurff is a Microsoft Certified Trainer for close to 20 years with experience teaching Microsoft SQL Server, Exchange Server, and Windows Server certification courses. He has been a database and networking consultant for a majority of that time, and currently holds over 48 Microsoft certifications. John is an MCT Regional Lead for the eastern United States, and formerly a Data Platform MVP.

 

About this video

Author(s)
John Deardurff
DOI
https://doi.org/10.1007/978-1-4842-4993-2
Online ISBN
978-1-4842-4993-2
Total duration
41 min
Publisher
Apress
Copyright information
© John Deardurff 2019

Related content

Video Transcript

Welcome to this T-SQL Querying video on data manipulation language. My name is John Deardruff. I’ve been a Microsoft Certified Trainer for 20 years. I’m a former MVP on Data Platform. And I have quite a few certifications. In this video, we’re going to have this brief introduction. We’ll discuss what is data manipulation language, or DML. We’ll discuss how to insert records into a table, insert records into an identity field within the table, inserting records into sequence fields between tables, and then inserting records from existing tables. Then we’ll discuss some other DML statements such as updating, DELETE, the differences between DELETE versus DROP versus TRUNCATE, using the output clause with our insert update and DELETE statements, and then finally the MERGE statement.

Some prerequisites for this video is a knowledge of writing a T-SQL statement, or have watched my previous video on writing a SELECT statement. You will also need a copy of SQL Server and the Adventure Works database to practice. To get SQL Server– and or the Adventure Works database– you can go to this docs.microsoft.com link to download an evaluation edition of SQL Server. Or you can use Azure SQL database, and then you could download the Adventure Works database from the same location. So we hope you enjoyed this video series on data manipulation language.