How to use INSERT in SQL
Storing data is of utmost importance for reproducing the same whenever the need arises and for several other business purposes. These data are stored inside tables, and related tables are grouped together in an organized way inside the databases. Now, for storing data in the tables, we need to use the insert statement of SQL. Let’s understand the insert statement with examples.
Author: Nishi Paul
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
Description
Suppose you are working as a database manager at any book store. You have the duty of storing every possible information about the books in the store. You need to add information to the table about the new books stocked in the store. You may also have to maintain the records of the customers visiting and purchasing in your store. Thus there are a lot of data to store, but all of it has to be done in an organized way so that you do not lose or mix up any data.
What can you do for this purpose? You can create a database for your book store and multiple tables inside the database for storing the required data in an organized way. Let’s say the name of your database is book_store. Inside this database, you have the table named books, that is having data for the books in your store. That is,
CREATE DATABASE book_storeCREATE TABLE books ( Book_no INT, Name VARCHAR(250), Author VARCHAR(100), Price FLOAT, Genre VARCHAR(100));
Must Read: Introduction to SQL
Must Read: SQL ACID Properties
You can create as many tables as you like inside this database for storing a different set of information other than books, like customer records, purchase patterns, etc.
So, you will get a table of books like this –
Now, you have just created a table. At this moment, the table is empty with five columns of Book Number, Name of book, Author name, Price of books, and Genre. The next step will be to store the data in the table. For storing data, we need to use the Insert statement of SQL. Let’s see how we can use the Insert Statement.
INSERT Statement
Insert statement is for inserting data into the tables. Suppose your store has ten different books, which means you have ten unique records. Each row in a table is called a record, and each column in a table is called a field. Thus, the table of books has five fields, and we will insert ten records in it, that is, it will have ten rows of data. Let’s insert ten records into the table now.
The syntax for the insert statement is –
INSERT INTO table_name (column_name1, column_name2, …..) VALUES (value1, value2, ……)
Now you can insert data into the table in two ways –
- By specifying the column names after the table name (just as shown in the syntax above)
- By not using the column names. In this case, the syntax will be –
INSERT INTO table_name VALUES (value1, value2, ……), which is the same as the previous one but without the column names.
Let’s see how to insert data using the column names.
INSERT INTO books (Book_no, Name, Author, Price, Genre) VALUES (1, 'Da Vinci Code', 'Dan Brown', 600, 'Mystery'), (2, 'The Day of the Jackal', 'Frederick Forsyth', 750, 'Mystery'), (3, 'In the Woods', 'Tana French', 540, 'Mystery');
This will give us the table as such –
Thus, we can see that the values being inserted are in correspondence with the position of the column names. For example, in the table of books, we have placed 2 in the field of Book_no and 750 in the field price. If we would have written the insert statement in this way – INSERT INTO books (Book_no, Name, Author, Price, Genre) VALUES (750, ‘The Day of the Jackal’, ‘Frederick Forsyth’, 2, ‘Mystery’) then we would have got Book_no as 750 and price as 2. You would never want to sell your book at 2 rupees by making a considerable loss! Hence it is important to insert the values in the same order in which the column names are written, that is, the position of the values should be the same as the position of the column names.
Must Read: SQL WHERE
Must Read: Introduction to Normalization
Now let us change the ordering of column names. In that case, we will change the positioning of the values accordingly. Let us see how it is done.
INSERT INTO books (Price, Author, Name, Book_No, Genre) VALUES (490, 'Charlotte Brontë', 'Jane Eyre', 4, 'Love'), (380, 'Lavyrle Spencer', 'Morning Glory', 5, 'Love'), (510, 'Beverly Jenkins', 'Indigo', 6, 'Love');
This will give us the table as such –
Thus, we can see that the column name ordering is not the same as before. We have given the price first, then the author’s name, book name, book number, and genre. In this case, we have to provide the values in a similar ordering for the accurate insertion of data, that is, for inserting the data at its respective fields for every book. However, as you can see from the image above, the data gets displayed as per the order of columns in the table of books. This concludes that while writing the column names in the insert statement, the values should match the ordering of the column names. The values will get stored in the respective fields but will get displayed as per the table structure.
Now, let’s insert the data without writing the column names. In this case, you need to insert data as per the ordering of the columns in the table structure. While creating the table, we have done the column order as such – book_no, name, author, price, and genre. Thus, we will insert data in the table maintaining this order, otherwise we will end up with wrong table information. This is how you will do it –
INSERT INTO books VALUES (7, 'LORD OF THE LAST HEARTBEAT', 'May Peterson', 680, 'Horror'), (8, 'RESTLESS SPIRITS', 'JORDAN L. HAWK', 760, 'Horror'), (9, 'THERE’S SOMEONE IN YOUR HOUSE', 'STEPHANIE PERKINS', 580, 'Horror'), (10, 'Shortcake', 'Lucy Watson', 380, 'Comedy');
This will give us a table like this –
Thus, we can see that the data has got inserted into the respective columns. This is how we insert data into tables. We have stored the records of ten books in the table. For any additional book we may have, we can similarly store the values using the insert statement, keeping in mind the ordering. We can insert as many records as we like. Also make sure to comply with the data type of the declared field, otherwise you may end up in any error.
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
Inserting Data using Another Table
Suppose you want to create another table wherein you want to place the number of books in stock. You do not want to change or alter the existing table of books, but you want to create another table with the columns of book number, book name, and the number of books in stock. Let’s say that the name of the new table is book_stock. As you have to insert a similar book name and book number in the table of book_stock as in the table of books, you would not like to write the same data again. Here we will use the INSERT INTO statement, which will fetch the data for these two fields from the books table and store similar data in the book_stock table. Now we have the third field of the number of books in stock as well, which we have to fill manually. Let’s see how it is done.
Must Read: SQL WHERE
Must Read: Introduction to Normalization
Syntax for INSERT INTO statement is –
INSERT INTO table_two (column_names)SELECT column_names FROM table_one
Thus, we will select the desired columns from the first table and insert all the records for those selected columns in the second table. For this, we have to create the structure of the second table first, and then we can proceed with the insert into the statement. Let’s create the table book_stock –
CREATE TABLE book_stock ( Book_no INT, Book_Name VARCHAR(300), Number_of_books INT);
This will give us a table like this –
Now, let’s populate this table with the columns of Book_No and Name from the books table. Book_No from book_stock is the same as the Book_No in books, and Book_Name in book_stock is the same as the field Name in Books.
INSERT INTO book_stock (book_no, book_name)SELECT book_no, name FROM books
This will give us a table like this –
As you can see, the book_no and book_name fields are populated as we wanted. The column of number_of_books has all null values as we have not inserted anything in it. Let’s say we have stocked books as per the numbers 10, 12, and 8. Then we can insert the records for the books in this table as such –
UPDATE book_stockSET number_of_books = 10WHERE book_no in (1,3,5);
UPDATE book_stockSET number_of_books = 12WHERE book_no in (2,4,6,8);
UPDATE book_stockSET number_of_books = 8WHERE book_no in (7,9,10);
This will give us the table as such –
Thus, we have got the table we wanted. Here we are updating the table of book_stock using the UPDATE statement and setting the value to either 10, 12, or 8 by using the SET statement. We have placed the condition using the WHERE statement, which is basically for allocating the number of books per book.
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
Summary
Insert statement is essential for inserting records in the table. Every row in the table is called a record, and every column is called a field. We can insert data by either specifying or not specifying the column names. The ordering of the data insertion should match the order of the columns to avoid any errors.
Inserting into a statement allows us to create a table from an existing table as well by fetching the records from the first table, making it easier in terms of data insertion in the table. Insert statements are important to understand, as it is a way to insert data in your table. In real life, we use the insert statement in the same way as we have used in our example of the book store. The only difference is that here we have ten records, but in reality, we will have thousands to even millions of records.
This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio
Top Picks & New Arrivals
