Insert Data Only in Specified Columns
The insert into statement can be with or without all column names:
INSERT INTO Persons( P_Id, LastName, FirstName, Address, City ) VALUES( 4 ,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger' ); |
One row is added after the above insert into statement executes:
| P_Id | LastName | FirstName | 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 P_Id, LastName, and FirstName columns:
INSERT INTO Persons( P_Id, LastName, FirstName ) 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.
| P_Id | LastName | FirstName | 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:
|