7. DML Deep Dive — INSERT, UPDATE, DELETE & TRUNCATE

Getting Ready in SSMS

Open SSMS and connect to your server using Windows Authentication. In Object Explorer, expand Databases and confirm VoidX_Academy is there with the Department and Student tables from Modules 5 and 6.

Click New Query to open a fresh editor. Run this first:

USE VoidX_Academy;

Press F5. The database dropdown in the top-left of SSMS now shows VoidX_Academy. All commands in this module will run inside this database.

INSERT — Putting Data Into a Table

The INSERT command adds new rows to a table. The basic structure is always the same: the table name, a list of column names, and a matching list of values.

Insert one department:

INSERT INTO Department (DeptID, DeptName, Location)
VALUES (1, 'Computer Science', 'Science Block A');

Press F5. The Messages tab should say: (1 row affected). That confirms one row was added successfully.

Let us examine every word of this command so you understand it completely.

INSERT INTO Department — the keyword INSERT INTO tells SQL Server you are adding data, and Department is the table receiving the new row.

(DeptID, DeptName, Location) — this is the column list. You are explicitly telling SQL Server which columns the values belong to. The order of this list matters — the values must appear in the same order.

VALUES (1, 'Computer Science', 'Science Block A') — these are the actual values being inserted. The number 1 goes into DeptID, the text Computer Science goes into DeptName, and Science Block A goes into Location. Text values must always be wrapped in single quote marks.

INSERT — Adding Multiple Rows at Once

You can insert several rows in a single INSERT statement by separating each set of values with a comma. This is much more efficient than running a separate INSERT for each row because it makes only one trip to the database instead of several.

INSERT INTO Department (DeptID, DeptName, Location)
VALUES
  (2, 'Information Technology', 'Science Block B'),
  (3, 'Mathematics', 'Math Building'),
  (4, 'Physics', 'Science Block C');

Press F5. The Messages tab says: (3 rows affected). Three rows were inserted with a single command.

To verify all your departments are there, run a quick SELECT — which you will learn in full detail in Module 8:

SELECT * FROM Department;

A results grid appears at the bottom of SSMS showing all four departments.

INSERT — When Column Names Are Optional

If you provide values for every column in the table in the exact order they were defined, you can omit the column list. SQL Server assumes you are providing all values in column order.

INSERT INTO Department
VALUES (5, 'Chemistry', 'Science Block D');

This works — but it is fragile. If the table structure ever changes and columns are added or reordered, this command will fail or insert data into the wrong columns. Best practice is to always write the column list explicitly. It takes a few extra seconds but makes your code reliable and clear to anyone reading it later.

INSERT — Working With IDENTITY Columns and Defaults

In Module 5, you created the Student table with an IDENTITY column for StudentID and DEFAULT constraints for IsActive and EnrolledAt. When inserting into a table with these features, you simply leave those columns out of your INSERT statement entirely.

INSERT INTO Student (FirstName, LastName, Email, DateOfBirth, DeptID)
VALUES ('Kofi', 'Mensah', 'kofi.mensah@ucc.edu.gh', '2002-05-14', 1);

Press F5. Notice StudentID is not in the column list — SQL Server generates it automatically. IsActive is not listed either — it defaults to 1 (active). EnrolledAt is not listed — it defaults to the current date and time.

Now add two more students in one statement:

INSERT INTO Student (FirstName, LastName, Email, DateOfBirth, DeptID)
VALUES
  ('Ama',   'Darko',   'ama.darko@ucc.edu.gh',    '2003-01-22', 2),
  ('Kwame', 'Asante',  'kwame.asante@ucc.edu.gh', '2001-11-08', 1);

Press F5. Check the results:

SELECT * FROM Student;

You will see three students with automatically generated StudentIDs 1, 2, and 3.

INSERT — Deliberately Inserting NULL

When a column has no NOT NULL constraint, you can explicitly insert NULL to represent the absence of a value. Add a student who has not yet been assigned to a department:

INSERT INTO Student (FirstName, LastName, Email)
VALUES ('Efua', 'Boateng', 'efua.boateng@ucc.edu.gh');

DeptID is left out of the column list — since it has no DEFAULT and no NOT NULL constraint, it becomes NULL automatically. You can also write it explicitly:

INSERT INTO Student (FirstName, LastName, Email, DeptID)
VALUES ('Yaw', 'Amponsah', 'yaw.amponsah@ucc.edu.gh', NULL);

Both approaches result in the same thing — a student with no department assigned. NULL is not zero and not an empty string. It is the deliberate absence of a value, and SQL Server treats it differently from any other value.

INSERT — Using SSMS to Edit Data Directly

SSMS has a built-in data editor that allows you to insert, edit, and delete rows without writing any SQL at all. This is useful for quick data entry during development and testing.

In Object Explorer, right-click the dbo.Student table and select Edit Top 200 Rows. A grid opens showing the first 200 rows of the table. At the bottom of the grid there is an empty row marked with an asterisk — this is the new row entry point.

Click the empty cell in the FirstName column of that new row and type a name. Tab across to fill in the other columns. When you press Tab past the last column or click on another row, SSMS automatically commits the new row to the database. NULL columns appear as empty cells in the grid.

This editing grid is useful for small amounts of data during development. For bulk data or production systems, always use proper INSERT statements.

UPDATE — Changing Existing Data

The UPDATE command modifies the values in existing rows. It always follows the same structure: specify the table, specify the new values with SET, and specify which rows to change with WHERE.

Update Kofi's email address:

UPDATE Student
SET Email = 'kofi.m@ucc.edu.gh'
WHERE StudentID = 1;

Press F5. The Messages tab says (1 row affected). Verify the change:

SELECT StudentID, FirstName, Email
FROM Student
WHERE StudentID = 1;

The email address in the result grid now shows the new value.

You can update multiple columns in a single UPDATE statement by separating them with commas in the SET clause:

UPDATE Student
SET Email = 'ama.d@ucc.edu.gh', DeptID = 1
WHERE StudentID = 2;

This changes both the email and the department of student number 2 in one operation.

UPDATE — The WHERE Clause Is Not Optional

The WHERE clause in an UPDATE tells SQL Server which rows to modify. It is technically optional — but omitting it is almost always a mistake.

Consider what this command does:

UPDATE Student
SET DeptID = 3;

Without a WHERE clause, this updates the DeptID of every single student in the table to 3 simultaneously. If your table has 50,000 students, all 50,000 now have DeptID 3. There is no warning, no confirmation dialog — it just happens.

A safe approach during development is to run the equivalent SELECT first to see which rows will be affected:

SELECT StudentID, FirstName, DeptID
FROM Student
WHERE StudentID = 2;

Once you confirm the SELECT returns exactly the rows you intend to update, change SELECT to UPDATE and execute.

UPDATE — Using SSMS to Edit Rows Directly

In Object Explorer, right-click dbo.Student and select Edit Top 200 Rows. The data grid opens. Click directly on any cell to edit the value. When you click away to another row or press Tab to move, SSMS commits the change to the database immediately.

To cancel an edit before committing it, press Escape while still in the cell. Once you click away, the change is committed and cannot be undone from the grid — you would have to run a manual UPDATE to reverse it.

This visual editor also shows NULL values as empty grey cells. Clicking a grey NULL cell and typing a value replaces it. Pressing Delete on a cell with a value replaces it back with NULL — only if the column allows NULL.

DELETE — Removing Specific Rows

The DELETE command removes entire rows from a table. Like UPDATE, it must always be used with a WHERE clause unless you deliberately intend to remove every row.

Remove the student whose StudentID is 5:

DELETE FROM Student
WHERE StudentID = 5;

Press F5. The Messages tab confirms (1 row affected). That row is permanently gone from the table.

You can use any valid condition in the WHERE clause. Delete all students who have never been assigned to a department:

DELETE FROM Student
WHERE DeptID IS NULL;

This removes every row where DeptID contains NULL — potentially many rows at once if several students were unassigned.

The same safe practice applies here as with UPDATE. Run the equivalent SELECT first to see exactly which rows will be deleted:

SELECT StudentID, FirstName
FROM Student
WHERE DeptID IS NULL;

Once you are satisfied the SELECT returns only the rows you intend to remove, change SELECT * to DELETE FROM and remove the column list.

DELETE vs TRUNCATE — Understanding the Difference

Both DELETE and TRUNCATE remove rows from a table, but they work very differently and are appropriate in different situations.

DELETE removes rows one by one. It logs every individual deletion in the transaction log. This makes it slower on large tables, but it means DELETE can be rolled back if something goes wrong. You can target specific rows with a WHERE clause. DELETE also respects foreign key constraints — SQL Server will not let you delete a row that another table is referencing through a FOREIGN KEY.

TRUNCATE removes all rows from a table in one operation by deallocating the data pages. It is significantly faster than DELETE on large tables because it does not log individual row deletions. However, TRUNCATE cannot be used with a WHERE clause — it always removes everything. TRUNCATE also resets the IDENTITY counter back to its seed value, while DELETE does not. And TRUNCATE cannot be used on a table that has foreign key constraints referencing it from another table.

Remove all rows from a table using TRUNCATE:

TRUNCATE TABLE Department;

This empties the Department table entirely. The table structure — columns, constraints, indexes — remains perfectly intact. Only the data is gone.

The decision between DELETE and TRUNCATE comes down to three questions. Do you need to remove specific rows rather than all of them? Use DELETE. Do you need the operation to be reversible within a transaction? Use DELETE. Are you clearing a large table as fast as possible and you do not need row-level logging? Use TRUNCATE.

Understanding What Happens in the Transaction Log

Every INSERT, UPDATE, and DELETE you run is recorded in SQL Server's transaction log — a sequential file that captures every change made to the database. This log is what makes recovery possible. If a server crashes mid-operation, SQL Server uses the transaction log to either complete or undo the interrupted operation when it restarts.

For you as a developer, the most important implication is this: every DML command you run can be wrapped in a transaction. If something goes wrong, you can roll back the transaction and the data returns to its state before you started. You will explore transactions in depth in Module 13. For now, understand that DML commands are never truly invisible — SQL Server always knows what happened and can reverse it under the right conditions.