Part 5: INSERT, UPDATE, and DELETE Commands

Microsoft SQL Server Tutorial Series: Beginner to Expert

Welcome to Part 5 of our SQL Server tutorial series! In the last lesson, you learned how to retrieve data using the SELECT command. Now, it's time to learn how to add, modify, and remove data in your database using the core SQL commands: INSERT, UPDATE, and DELETE.

📘 What You’ll Learn in This Tutorial:

How to insert new records using INSERT
Update existing data using UPDATE
Remove unwanted records using DELETE
Best practices for data manipulation
Real-world use cases

🗃️ Sample Table: Students

We’ll continue using the Students table from SchoolDB. Here’s a quick reminder of the structure:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    IsActive BIT
);

➕ INSERT – Adding New Records

To add a new student:

INSERT INTO Students (StudentID, FirstName, LastName, BirthDate, IsActive)
VALUES (2, 'Brian', 'Smith', '2004-06-10', 1);
GO

💡 Tip: Always include all columns (except auto-incremented ones, if any).

✏️ UPDATE – Modifying Existing Records

To update a student’s name or status:

UPDATE Students
SET FirstName = 'Bryan', IsActive = 0
WHERE StudentID = 2;
GO

Note: Always use a WHERE clause to avoid updating all rows!

❌ DELETE – Removing Records

To delete a student:

DELETE FROM Students
WHERE StudentID = 2;
GO

⚠️ Warning: Omitting WHERE deletes all rows. Be careful!

📊 Example Table Before and After


-- Before Update/Delete
+-----------+----------+---------+------------+----------+
| StudentID | FirstName| LastName| BirthDate  | IsActive |
+-----------+----------+---------+------------+----------+
|     2     | Brian    | Smith   | 2004-06-10 |    1     |

-- After Update
|     2     | Bryan    | Smith   | 2004-06-10 |    0     |

-- After Delete
(No row with StudentID = 2)

🧾 Quick SQL Cheat Sheet

-- Add new data
INSERT INTO TableName (Col1, Col2, ...) VALUES (Val1, Val2, ...);

-- Change existing data
UPDATE TableName SET Column = NewValue WHERE condition;

-- Remove data
DELETE FROM TableName WHERE condition;

🌍 Real-World Example: Managing Enrollments

Imagine a student drops out or changes details. You can:

INSERT new students when admitted
UPDATE IsActive to 0 when a student leaves
DELETE records that were entered by mistake

📋 Best Practices for Data Modification

Tip	Why It’s Important
Always use WHERE	Prevents accidental mass updates/deletions
Test with SELECT first	Run a SELECT query with the same WHERE clause before modifying
Use transactions (advanced)	Allows rollback of changes if something goes wrong
Keep backups	Vital before major changes in production

✅ Summary

Use INSERT to add new records
UPDATE modifies existing data (with WHERE!)
DELETE removes rows (also with WHERE)
Always test queries and follow best practices

🔗 Navigation

Have questions? Ask below or try the queries in SSMS for practice!

← Previous Next →

Learn Python

Menu

SQL Server INSERT, UPDATE, DELETE Tutorial – Modify Data Easily