Insert Data Only in Specified Columns
The insert into statement can be with or without all column names:
INSERT INTO person ( person_id, last_name, first_name, address, city ) VALUES ( 4 ,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger' ); |
One row is added after the above insert into statement executes:
| person_id | last_name | first_name | address | city |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| 4 | Nilsen | Johan | Bakken 2 | Stavanger |
It is also possible to only add data in specific columns. The following SQL statement will add a new row, but only add data in the person_id, last_name, and first_name columns:
INSERT INTO person ( person_id, last_name, first_name ) VALUES ( 5 ,'Tjessem', 'Jakob' ); |
The columns not specified are inserted with values
NULL, which represents missing unknown data.
NULL values are treated differently from other values.
It is used as a placeholder for unknown or inapplicable values.
| person_id | last_name | first_name | address | city |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| 4 | Nilsen | Johan | Bakken 2 | Stavanger |
| 5 | Tjessem | Jakob |
Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.
|
Result:
|
The Database includes:
|
|
The Database includes:
|