In this article, we’ll discover data modification statements used to modify data in SQL server using the INSERT
, UPDATE
, DELETE
, and MERGE
statements.
The SELECT
, INSERT
, UPDATE
, DELETE
, and MERGE
statement are collectively referred to as DML (Data Manipulation Language) statements. These statements allow you to view and modify data. We extensively cover SELECT in other articles. In this article, we look at how we can add, modify, DELETE
data with the INSERT
, UPDATE
, and DELETE
statements. Finally, we’ll wrap up the discussion with MERGE
.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012
database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
INSERT Statement
The INSERT
statement is used to add one or more rows to a database table.
Data can either be inserted one row at a time or in bulk. You can also use insert to add rows from one table into another.
The insert
statement can be part of a transaction. Based on the success of the transaction, you can either COMMIT
or ROLLBACK
the changes.
Each time an insert
is executed, the @@ROWCOUNT
is updated.
The basic structure of the INSERT
statement is:
INSERT INTO tableName
(column1, column2, …)
VALUES (value1, value2, …)
As you can see, the basic structure for the insert
statement is to:
- specify the table we wish to insert rows into
- the columns we wish to populate
- the values to insert
Let’s look at a specific example. In this simple example, we’re going to insert a row into the Department
table.
Notice that we’re specifying the table, columns, and values we wish to insert.
BEGIN TRANSACTION
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('Risk Management', 'Executive General and Administration')
SELECT Name, GroupName
FROM HumanResources.Department
ROLLBACK
When you run this, you see the newly inserted row below:
You may be wondering why I have wrapped my example in a transaction. I did this so I don’t permanently save my changes. If I didn’t, I could only run this particular example once!
The transaction starts with BEGIN TRANSACTION
and is reversed out with ROLLBACK
.
It is a good trick you can use when you want to test out an insert
but don’t want it to persist. Of course, all changes should be tested in a development or test environment! Never test within your production environment. If you want to dig deeper, then check out my article Introduction to the Insert Statement.
UPDATE Statement
The UPDATE
statement is used to change a column value for one or more database table rows.
Since the update
statement can affect one or more rows, you should take great care in making sure you are updating the rows you wish!
Like the INSERT
statement, update
can be part of a transaction. Based on the success of the transaction, you can either COMMIT
or ROLLBACK
the changes.
Once an UPDATE
statement completes, @@ROWCOUNT
is updated to reflect the number of rows affected.
The basic structure of the UPDATE
statement is:
UPDATE tableName
SET column1=value1, column2=value2,...
WHERE filterColumn=filterValue
The UPDATE
statement is typically in three parts:
- The
tableName
to update - The
SET
clause which specifies the columns to update - The
WHERE
clause, which specifies which rows to include in the update operation
Let’s look at a specific example. In this simple example, we’re going to update a row into the Department
table.
Let assume that the director of Human Resources wants to change the Information Services department name to Information Technology.
Here is the UPDATE
statement you could use:
BEGIN TRANSACTION
UPDATE HumanResources.Department
SET Name = 'Information Technology'
WHERE DepartmentID = 11
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ROLLBACK
Note: I wrapped my example in a transaction so that I don’t permanently alter my sample database.
Here, you can see the newly updated value.
Notice that the UPDATE
statement specifically targets the row using the primary key DepartmentID
. By doing this, I can guarantee I’m updating the correct row.
Care should be taken when writing the UPDATE
statement. You really need to be sure the WHERE clause is including only those rows you wish to update. Many people have been burned by incorrectly writing the where
clause.
Before I write my UPDATE
statement, I usually test out the WHERE
clause using a SELECT
. If the select
returns the correct rows, I can be sure the update
’s scope is correct.
DELETE Statement
The DELETE
statement is used to remove one or more rows from a database table.
Since the DELETE
statement can affect one or more rows, you should take great care in making sure you’re deleting the correct rows!
Like the INSERT
statement, the DELETE
statement can be part of a transaction. Based on the success of the transaction, you can either COMMIT
or ROLLBACK
the changes.
Once a delete
statement completes, @@ROWCOUNT
is updated to reflect the number of rows affected.
The basic structure of the DELETE
statement is:
DELETE tableName
WHERE filterColumn=filterValue;
The DELETE
statement is typically in two parts:
- The
tableName
to update - The
WHERE
clause, which specifies which rows to include in the update
operation
Let's assume that the director of Human Resources wants to remove all pay history changed before 2002.
Here is the DELETE
statement you could use:
BEGIN TRANSACTION
DELETE HumanResources.EmployeePayHistory
WHERE RateChangeDate < '2002-01-01'
ROLLBACK
Of course before you run this, I would test with a SELECT
statement. This is the statement I would run to make sure what I’m intending to delete is correct.
SELECT BusinessEntityID, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE RateChangeDate < '2002-01-01'
When you run this statement, the specified rows are removed from the table.
MERGE Statement
The MERGE
statement provides a means to perform, in one statement, an INSERT
, UPDATE
, or DELETE
operation on one table based on source data from another.
The main advantage to using the MERGE
statement is that when it executes, it makes one pass through the data, as opposed to a pass for each operation (e.g., three passes for separate INSERT
, UPDATE
, and DELETE
operations).
The basic structure for the MERGE
statement is:
MERGE targetTable
USING sourceTable
ON joinCondition
WHEN MATCHED --update
WHEN NOT MATCHED --insert
WHEN NOT MATCHED SOURCE --delete
The basic idea is to perform an INSERT
, UPDATE
, or DELETE
operation on the targetTable
, using the sourceTable
as input. The targetTable
and sourceTable
rows are matched to one another according to the join
condition.
Depending upon the match, then INSERT
, UPDATE
, or DELETE
statements are executed.
For example, if you are looking to update one table with values from another, then “WHEN MATCHED
” it would make sense to update the rows, and “WHEN NOT MATCHED SOURCE
” delete the row from the target, as it wasn’t found in the source.
Let’s look at an example. Suppose you have a list of updated vendor data. It consists of new and update information. Depending on whether we find a BusinessEntityID
in Purchasing.Vendor
, we’ll either want to INSERT
or UPDATE
the data.
We’ll use an implicit table for our source, but you can easily imagine that being another table in the database.
BEGIN TRANSACTION
MERGE Purchasing.Vendor V
USING (Values (1492, 'AUSTRALI0001','Australia Bike LLC', 1,1,1),
(100, 'AUSTRALI0002','Australia Cycle', 2,0,1 ) )
AS SOURCE (BusinessEntityID, AccountNumber, Name, CreditRating, PreferredVendorStatus, ActiveFlag)
ON V.AccountNumber = Source.AccountNumber
WHEN MATCHED THEN
UPDATE SET V.Name = Source.Name,
V.CreditRating = Source.CreditRating,
V.PreferredVendorStatus = Source.PreferredVendorStatus,
V.ActiveFlag = Source.ActiveFlag,
V.ModifiedDate = GETDATE()
WHEN NOT MATCHED THEN
INSERT (BusinessEntityID, AccountNumber, Name,
CreditRating, PreferredVendorStatus, ActiveFlag, ModifiedDate)
VALUES (Source.BusinessEntityID, Source.AccountNumber, Source.Name,
Source.CreditRating, Source.PreferredVendorStatus, Source.ActiveFlag, GETDATE());
ROLLBACK
Here are the steps, in general that SQL takes, to process the statement.
For each row in Source.
- Match the source row
AccountNumber
to Purchasing.Vendor.AccountNumber
. - If there is a
MATCH
then UPDATE
the Vendor
with Source
column values. - If there is no
MATCH
then INSERT
a new Vendor
using Source
column values.
Hopefully, you can see that the MERGE
statement is very powerful. It can be used to combine several operations, which, if used without the MERGE
would require you to write a stored procedure using conditional logic. This in itself isn’t, bad, but it does make it simpler to write code.
Do worry if you don’t completely understand how merge works, we’ll revisit in detail in another article.
Conclusion
This article serves as an introduction to various ways you can use SQL to modify data within your database. We dig deeper into each method in follow-up articles:
The post Introduction to SQL Server Data Modification Statements appeared first on Essential SQL.