INSERT INTO statement in SQL

INSERT INTO is the DML statement for inserting records into database tables. The basic statement is very simple and there are two ways to do it:

--Option 1
INSERT INTO tableName (column1, column2...) VALUES (value1, value2, ...)

--Option 2
INSERT INTO tableName VALUES (value1, value2, ...)

The main difference is that in the first option we can add the records according to the columns that we want to report only, while the second option forces us to report all the values of the columns of the table according to their position, even if they have a NULL value.

Based on this syntax, we are going to see some examples on our Customers and Orders tables.

INSERT INTO

Reporting name of the columns

Once we have our tables created using the CREATE TABLE statement, we can proceed to fill them with new records with the INSERT INTO statement. Next, we will leave the example scripts that we have used to fill our Customers and Orders tables:

• INSERT INTO Customers:

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Janice', 'Haines', '4822 Radio Park Drive', 111222333);

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Mavin', 'Petitt', '2336 cottonwood Lane', 123456789);

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Peter', 'Davis', '3608 Sycamore Lake Road', 998344567);

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Helen', 'Ward', '711 Hershell Hollow Road ', 447824556);

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Kimberly', 'Lee', '4298 Drummond Street', 778345112);

We can see the inserted new records from the following query:

SELECT * FROM Customers;

• INSERT INTO Orders:

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Xiami Mi 11', 'Smartphone', '286.95', '2022-07-09', 15, 3);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Play Station 5', 'Sony Game Console', '549.95', '2022-07-18', 4, 2);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Xbox series X', 'Xbox Game Console', '499.99', '2022-01-21', 2, 2);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('MacBook Pro M1', 'Apple Notebook', '2449.5', '2022-06-20', 1, 1);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Echo DOT 3', 'Alexa Speaker', '39.99', '2022-09-01', 50, 4);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Echo DOT 4', 'Alexa Speaker', '59.99', '2022-09-01', 50, 4);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Nintendo Switch', 'Nintendo Game Console', '299.99', '2022-09-05', 3, 5);

We can see the new records that we just inserted from the following simple query:

SELECT * FROM Orders;

Since the customerId and orderId fields are declared with the IDENTITY property, they should not be reported in the columns of the INSERT INTO statements. The system will automatically take care of giving them an auto-incremental value according to the definition of said property in the table declaration.

Since the totalPrice field is specified as a mathematical operation resulting from the multiplication of the price and productQuantity fields, it is exempt from being specified in the sentence.

Without informing column names

This type of INSERT INTO can be useful when we want to inform most or all of the fields of a table, since on some occasions it can save us time writing the statement. However, it should be noted that it has some limitations that sometimes make it an option to discard.

The peculiarity of this INSERT INTO, as we have already mentioned, is that it forces all the fields of the table to be taken into account, giving them a value in the appropriate position in relation to each column. This includes explicitly specifying NULL and empty fields that we don’t want to report.

In addition, in the event that we have auto-incremental fields with the IDENTITY property, we will not be able to use this option, since the system forces us to specify the column to which the property refers, and it is not possible to establish it with the position. Therefore we cannot use this INSERT INTO to add a new record to our example tables Customers and Orders, since both are declared with fields with IDENTITY property.

Although the following INSERT INTO will fail due to the definition of our model, it syntactically exemplifies how this type of insertions are defined:

INSERT INTO Customers
VALUES (6, 'Janice', 'Janice Haines', '', NULL);

Break IDENTITY property

It is possible to break the IDENTITY property to indicate a specific value to the column it affects, breaking the default auto-increment sequence.

SET IDENTITY_INSERT Customers ON;

This statement allows us to specify the desired value to the customerId field:

INSERT INTO Customers
    (customerId,
     [name],
     lastName,
     [address])
VALUES
    (15,
    'Joseph',
    'Cruz',
    '4933 Colony Street');

To re-enable the default IDENTITY property, just run this statement:

SET IDENTITY_INSERT Customers OFF;

This way the next INSERT INTO on the Customers table will take the value customerId = 15 as a reference value to auto-increment by one on the next record.

Common mistakes

It is very important when performing any INSERT INTO, to know very well how the table has been declared along with its properties and restrictions to avoid easily preventable errors.

Properties

INSERT INTO Customers
    ([name],
     [address])
VALUES
    ('Elaine',
    '3728 Alpaca Way');

As the console message itself explicitly indicates, in our Customers table declaration, both name and lastName are declared with the NOT NULL property, so we must make sure to give them some value.

Constraints

Data control is very important, especially in tables that have Foreign Key defined to relate to other tables. When executing an INSERT INTO, we must not only pay attention to the data type and properties to write the statement, but we must also pay special attention to the constraints of the table and the consistency of the data.

INSERT INTO  Orders
    (product,
    productDescription,
    price,
    productQuantity,
    customerId)
VALUES
    ('Play Station 5',
    'Sony Game Console',
    '549.95',
    15,
    12);

This error is due to the fact that there is no record in the Customers table whose customerId is 12, which means that we are trying to relate a product to a client that does not exist in the system. This type of error, together with formatting errors in data types, is very common and recurrent in the first steps in managing relational databases and that is why we urge you once again to underline the importance of reviewing the data that we manage.