Create, Read, Update and Delete Data in SQL Server Tables

By:   |   Updated: 2022-09-15   |   Comments   |   Related: More > TSQL


Problem

When working with a database you need the ability to create, read, update and delete data, this is an acronym for CRUD operations. In this SQL tutorial, we will look at some basic examples of how to do this with our test HR database that we have been using to cover various aspects of building a database application.

Solution

In this tutorial, we will build upon the concepts we learned from this article. We will work through the following CRUD functionality - INSERT, UPDATE, DELETE, and SELECT statements and highlight the areas you should be aware of when updating or deleting data. We will be using the HRDatabase, tables and data from this previous article. All of these queries will be run in the HRDatabase we created in the previous article.

SELECT from Tables

To choose data from a SQL database, you use the SELECT statement, and the information returned is known as the result set.

To get all the data from the Companies table, you do a "select all from the companies table", in the example below we use an asterisk (*) which means all columns.

USE HRDatabase
GO

SELECT * FROM dbo.Companies

Remember that we created relationships between tables, such as one-to-one or one-to-many. For example, one Company can have multiple Employees, and many Employees can work at one Company.

You can run the below SELECT statements against the HRDatabase to return all of the data.

SELECT * FROM Companies
SELECT * FROM Employees
SELECT * FROM Title
SELECT * FROM Gender
SELECT * FROM LeaveTypes
SELECT * FROM Leave
SELECT * FROM Addresses

In the output below, I show how the data relates to other data.

Here is an explanation of the relationship between the Companies and Employees data (the first two outputs in the image below):

  • The id column from Companies has a link to the Employees CompID column
  • Anyone Company can have multiple Employees, which is a one-to-many relationship
Select statement results table

SELECT Data from Single and Multiple Tables

To combine data from two tables (Companies and Employees), you use a join, where we specify that the CompID values in the Employees table have to equal the ID values in the Companies table.

SELECT Companies.CompanyName
      ,Employees.EmployeeName
      ,Employees.ContactNo
      ,Employees.Email
      ,Employees.CompID
FROM Companies 
JOIN Employees ON Employees.CompID = Companies.id

See the following SQL Server JOIN Tables Tips for more information.

To make the code easier to write and read, we can also use aliases. Here is additional information on SQL aliases:

  • SQL Alias - SQL aliases are used to give a table, or a column in a table, a temporary name.
  • SQL – Alias Syntax - You can temporarily rename a table or a column by giving another name known as Alias. Table aliases are used to rename a table in a specific SQL statement. The renaming is a temporary change, and the actual table name does not change in the database. The column aliases are used to rename a table's columns for the purpose of a particular SQL query.

Here is an example where we alias Companies as "co" and Employees as "emp".

SELECT co.CompanyName
      ,emp.EmployeeName
      ,emp.ContactNo
      ,emp.Email
      ,emp.CompID
FROM Companies co
JOIN Employees emp ON emp.CompID = co.id

Here is another example with a WHERE clause:

-- Get all the Employees working for Alpha Company, add the following filter (where clause):
SELECT co.CompanyName
      ,emp.EmployeeName
      ,emp.ContactNo
      ,emp.Email
FROM Companies co
JOIN Employees emp ON emp.CompID = co.id
WHERE co.id = 1

INSERT a New Record into a Table

When your database is set up correctly using primary key and foreign key relationships, you will not be able to issue an insert statement for an employee that works for a company that does NOT exist. This also holds true for some of the other tables we setup that have foreign key relationships.

Let's check out one example.

SELECT * FROM Employees
SELECT * FROM LeaveTypes
SELECT * FROM Leave

Joe Blogs would like to take annual leave over the second half of December this year. Let's insert his leave.

INSERT INTO Leave (LeavetypeID, StartDate, EndDate, LeaveNotes, EmpID) 
VALUES (1, '2022-12-15', '2022-12-31', 'For a well-deserved break in South Africa Kruger National Park!', 1)

SELECT * FROM Leave

UPDATE the Data

When updating the data, you should always be careful and keep your focus. Always do a SELECT of the data that you want to work with first to test your WHERE clause.

IMPORTANT TIP: Always make sure an UPDATE, DELETE, or INSERT is preceded by comments (--), this is a personal preference!

It is easy to just press F5 to execute the contents of the query window and accidentally do a DELETE, UPDATE or INSERT that should not have happened!

Create the select statement of the data that you want to update:

SELECT *
FROM Employees e
WHERE CompID = 1 -- parameter

Make sure that the result set is the data you want to UPDATE, then add the UPDATE after some comments, as in the example below.

SELECT *
-- UPDATE e SET CompID = 2
FROM Employees e
WHERE CompID = 1 -- parameter

Now, you can highlight at the start of the UPDATE until after the WHERE clause, and then run it or press F5!

Always make sure you have a WHERE clause in your UPDATE!

query

DELETE Operations on a Table

Deleting some records is straightforward, but ALWAYS make sure you have a WHERE clause in the DELETE script!

SELECT *
FROM Employees

Again, you can add the DELETE with some comments in front to protect you from an UNWANTED DELETE!

SELECT *
-- DELETE 
FROM Employees
WHERE CompID = 4

I want to emphasize, when working with UPDATE, INSERT and DELETE, you should always be focused to ensure what you are doing!

query

Manipulating Data when Working with a JOIN

As in the examples above, create your join statement, add the UPDATE statement after some comments, and test your statement, first by highlighting the whole SQL statement, and run it.

When your result set has the records you want to update, highlight from the UPDATE until after the WHERE clause, and run it.

SELECT  c.id
      , c.CompanyName
      , e.EmployeeName
      , e.ContactNo
      , e.Email
      , e.CompID
-- UPDATE e SET CompID = 3
FROM Companies c
JOIN Employees e ON e.CompID = c.id
WHERE c.id = 2

Check your result set to make sure the UPDATE worked as expected.

SELECT  c.id
      , c.CompanyName
      , e.EmployeeName
      , e.ContactNo
      , e.Email
      , e.CompID
FROM Companies c
JOIN Employees e ON e.CompID = c.id
query

CASCADE Option when Defining the Foreign Keys

To conclude this tutorial, let's look at the CASCADE option when you define foreign keys. If you remember from the third tutorial, the CASCADE option in the foreign key takes care of handling associated data. Let's take a look!

Run the two selects below.

SELECT * FROM Companies
SELECT * FROM Employees
query results

As you can see, two employees are working at Alpha Company. What will happen if you delete Alpha Company, which has two employees working for it?

DELETE Alpha Company from the Companies table.

SELECT *
--DELETE
FROM Companies WHERE ID = 1

Check the Companies and Employees tables again.

SELECT * FROM Companies
SELECT * FROM Employees
query results

You can see that Alpha company has been removed and the two employees linked to Alpha company have been removed.

Foreign Key Settings

How can we check the actions defined on the foreign keys? Just use the command below.

EXEC sp_help Employees

See the delete_action and the update_action of the FK_Employees_Companies foreign key in the image below. It has been created with a CASCADE option on delete and update actions, meaning that the employees will be deleted when the company they are working for (connected to) is removed (deleted) from the Companies table.

foreign key settings

Clean up by dropping the HRDatabase database

Use this code to drop the HRDatabase if you don't want to keep it.

--Clean up the database
USE master
GO
 
ALTER DATABASE HRDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 
DROP DATABASE IF EXISTS HRDatabase
GO
Next Steps

In the next step, we will look at how to create Clustered and Non-Clustered indexes.

See the following articles as well:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Jan Potgieter Jan Potgieter has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies.

View all my tips


Article Last Updated: 2022-09-15

Comments For This Article

















get free sql tips
agree to terms