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
It is more efficient and recommended to use the first option, indicating only the columns that want to be informed.
Based on this syntax, we are going to see some examples on our
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
- 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;
|1||Janice||Haines||4822 Radio Park Drive||111222333|
|2||Mavin||Petitt||2336 cottonwood Lane||123456789|
|3||Peter||Davis||3608 Sycamore Lake Road||998344567|
|4||Helen||Ward||711 Hershell Hollow Road||447824556|
|5||Kimberly||Lee||4298 Drummond Street||778345112|
- 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;
|1||Xiami Mi 11||Smartphone||286,95||2022-07-09||15||4304,25||3|
|2||Play Station 5||Sony Game Console||549,95||2022-07-18||4||2199,8||2|
|3||Xbox series X||Xbox Game Console||499,99||2022-01-21||2||999,98||2|
|4||MacBook Pro M1||Apple Notebook||2449,5||2022-06-20||1||2449,5||1|
|5||Echo DOT 3||Alexa Speaker||39,99||2022-09-01||50||1999,5||4|
|6||Echo DOT 4||Alexa Speaker||59,99||2022-09-01||50||2999,5||4|
|7||Nintendo Switch||Nintendo Gane Console||299,99||2022-09-05||3||899,97||5|
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.
totalPrice field is specified as a mathematical operation resulting from the multiplication of the
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
Orders, since both are declared with fields with
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
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.
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.
INSERT INTO Customers ([name], [address]) VALUES ('Elaine', '3728 Alpaca Way');
Cannot insert the value NULL into column ‘lastName’, table ‘store.dbo.Customers’; column does not allow nulls. INSERT fails.
As the console message itself explicitly indicates, in our
Customers table declaration, both
lastName are declared with the
NOT NULL property, so we must make sure to give them some value.
It is important to make a clarification regarding the
NULL fields, since it is a fact that often leads to errors both in database work and in other programming languages. We must remember that they are not synonyms. In short, a
NULL field is an undeclared field, which has no memory space allocated, while an
empty field does point to a specified memory area, declaring it empty.
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);
The INSERT statement conflicted with the FOREIGN KEY constraint “fk_customers_orders”. The conflict occurred in database “store”, table “dbo.Customers”, column ‘customerId’
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.