Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Inserting and updating NULLs
5. Insert NULL into a column
Conditions in UPDATE and DELETE
Using values from another column
Return rows in INSERT, UPDATE, DELETE
Summary

Instruction

You learn quickly! Great! In SQL Server, we can also insert data into a table by listing all the column names. This requires us to include all values, both known and unknown.

Let's analyze a case from the university database. The teacher completes the list of students. He has to insert Tom Muller (Id = 12), but Tom doesn't have a middle name. Look at the statement:

INSERT INTO Student (Id, FirstName, MiddleName, LastName)
VALUES (12, N'Tom', NULL, N'Muller');

In this situation, NULL helps the teacher insert the record for the unknown value.

Since this command defines values for all columns in the table, you can simply use it this way:

INSERT INTO Student 
VALUES (12, N'Tom', NULL, N'Muller');

Which syntax should you choose: the first one or the shorter second one? It's entirely up to you! If you want to emphasize that you inserted a NULL value, insert it explicitly. If you want to save on typing, omit the column name.

Exercise

Add Tom Muller's written exam score in Mathematics to this table. You know only the exam ID (17), the student ID (12), Tom's score on the written exam (12), the date of the written exam (October 14, 2018), and when the exam was scored (October 16, 2018).

The other column values are unknown.

Note: Use this format for the date: YYYY-MM-DD. Here, YYYY is a year with 4 digits, MM is a month with 2 digits, and DD is a day with 2 digits. For May 12, 2017, we'd write 2017-05-12.

Stuck? Here's a hint!

You need to use an INSERT statement with values and NULLs.