78  SQL DML Insert

Insert statement is used to add rows to our tables. We have 3 different version of insert.

  1. insert into table values
  2. insert into table select
  3. insert into table DEFAULT VALUES;

See following database specific documentation

78.1 Insert into table values

First version is the most basic one.

INSERT INTO tablename (column name1, column name2...) VALUES (value1,value2...)

For the following Playlist table with two columns.

erDiagram
    Playlist {
        int PlaylistId PK
        text Name
    }

We use following insert into statement.

INSERT INTO Playlist (PlaylistId, Name)
VALUES(0, '');
  • We can omit autonumber (identity) columns since they get their values automatically
  • We can omit any column which allows null values from this list.
  • We can omit any column with default values from this list.

Thus following will also work since PlaylistId is autonumber.

INSERT INTO Playlist (Name)
VALUES('Name playlist');

If even one required column is missing from the statement, we will get an error, for example:

A NOT NULL constraint failed

We can not omit every column value and use empty values but we can use default values statement as below, when every column has a default value be it null, given default or autonumber.

78.2 Insert into Default values

In Playlist table, Column PlaylistId is autonumber and column Name allows nulls; therefore, we can use 3rd form default values, with the following statement

insert into Playlist DEFAULT VALUES;

Above statement insert null value to name column and PlaylistId gets next available int value.

78.3 Insert multiple rows

Values part allows us to insert multiple rows using multiple row values divided by comma values. Below insert 3 rows to Playlist table.

INSERT INTO Playlist (Name) VALUES
    ('Name 1'),
    ('Name 2'),
    ('Name 3');

following sql insert 4 rows to Customer table. CustomerId is auto number and other columns allows null values.

INSERT INTO Customer
(FirstName, LastName,  Email)
VALUES
('Atilla', 'Özgür', 'example1@ex.com'),
('Funda', 'Özgür', 'example1@ex2.com'),
('Deneme1', 'Deneme2', 'ex21@ex.com'),
('FirstName1', 'Lastname1', 'email1@ex.com')
;

78.4 Insert into select

Last form of Insert could use select statement instead of values clause. Our select statement should return all values required by the table. Since Customer table requires 3 columns we could use following statement to insert every employee as customer.

INSERT INTO Customer
(FirstName, LastName,  Email)
SELECT  FirstName,LastName, Email
FROM Employee;