Upto 20% Scholarship on Live Online Classes

- Technology Blogs -

What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)

  • »
  • SQL
  • »
  • What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)
What is SQL Insert Query

SQL or the Structured Query Language is used to create, manage, and retrieve the data from relational database systems. Now if you want to know what is a relational database system then it is associated with storing and retrieval of data from the tables of a relational database system.

There are a few queries in SQL that are used to store, retrieve, and update the information in the SQL and here in this blog, we are going to discuss the SQL insert query in detail. Though you may need all other queries to create and maintain the SQL tables, here we will discuss the insert query/command that is a basic statement and used to insert data into the database.

Firstly, here in this blog, we will discuss the relational database and then the syntax and various versions of this query along with examples.

Introduction to Relational Database

Relational database tables in which the data is stored are related to each other. Even the tables of the relational database are called relations. Like a table or relation named Employee is shown below:

Employee-id Name City Age
E01 Ramesh Delhi 32
E02 Sameer Bihar 34
E03 Sujit Rohtak 33
E04 Suresh Patna 29

 

For any relation or table of the database, some standard terms or terminologies are used by the programmers or database administrator. We will discuss them in our next section:

  • Attribute: Table attributes are the table properties like in above example the attributes of the Employee table are Employee-id, Name, Age, and City.
  • Tuple: Table rows are known as the tuple. In the above table, there are total 4 tuples as it has for rows and values
  • Cardinality: Total number of tuples of any table is known as its cardinality. The cardinality of the above table is four.
  • Column: Set of values for any particular attribute is contained in the column. If a column will be extracted from the above table like Name then it will become like:
Name
Ramesh
Sameer
Sujit
Suresh

What is the Insert Query in SQL?

Before we dive deep into the topics, let us have a quick look at the database languages first that are frequently used by database developers to store data with the help of different queries.

  • Data Definition Language: DDL or data definition language is used to define the database structure. Queries like Create Table, Add Column or Drop Column are included in these queries.
  • Data Manipulation Language: The queries that are used to manipulate the tables or relations are known as data manipulation language. These queries include Insert, Delete or Update
  • Data Query Language: To extract the data from the database, we can use these commands. The ‘Select’ query comes under this category.
Read:   Microsoft SQL Server Developer Role: Job Responsibilities & Description

Now, here we will discuss the insert query of SQL database in detail with an example. In order to understand this query, you must be familiar with the SQL queries like Create or Select.

This is quite clear the database systems are used to store data in tables. Table data is usually supplied either by application programs or other tables or directly by other tables. These data sources are used to supply data directly to the table. Here in SQL Insert command is used to insert or add data into the table. With the Insert command, a new row is created into the table that can store data.

The general Syntax of Insert Command

INSERT INTO table_name (columm1, column2, column3, – – – – -) VALUES (value1, value2, vaue3, – – -);

Here in this syntax various keywords and their details are listed below:

  • INSERT INTO ‘table_name’: is the command that is used and to inform the MySQL engine that the query is used to add new rows into the table ‘table_name’.
  • (column1, column2, column3, – – – ): It is used to specify a particular column that has to be updated in the new row.
  • VALUES (value1, value2, value3 – – -): It is used to specify the values that are to be added as the new row in the table.

Here, in the Insert query of SQL, there are some rules and norms that should be followed. They are listed below:

  • String Data Types: While inserting string values the values must be enclosed in single quote
  • Numeric Data Type: If you need to supply numeric values to the query then you need not enclose them in a single quote, even you can supply them directly while executing the query.
  • Date Data Type: Dates or values as data need to be enclosed in a single quote and the standard format that is used to insert the date values is ‘YYYY-MM-DD’

How to insert date, multiple rows, and values in a Table?

Let’s take an example of the following table and will use the INSERT command to add values.

Read:   Top 30 Data Modeling Interview Questions with Answers

Table: Student(S_id, DoB. Gender, Contact_No, City) (Let the table structure is like this)

Let’s insert a new row in this table, then below syntax will be used for this purpose:

INSERT INTO ‘STUDENT’(‘S_id’, ‘Gender’,’Contact_No’, City) VALUES (‘S_01’, ‘Male’, 098987898, Delhi);

When the above command will be executed than just except the DOB all values will be inserted in the table and there a new row will be formed. Here the values are inserted as numeric values are provided without quotes and string values are provided with the quotes. Here, in the above query, the contact number is a numeric value and is not enclosed within a single quote so here the zero will not be stored, while if you want to store zero along with the value then it is good to enclose the numeric value within a single quote.

Here, you can also change the order of column names in Insert query that will not have any impact in value insertion, you must here need to be careful about the order in which the values are inserted and the name of the column that is provided with the query statement. As here in this insertion, DOB value is not provided while creating the new row, so here NULL value will be inserted in this place. Now let us insert a new row in the same table along with DOB value. Here, make sure that the value of date must be enclosed within the single quote and in the format ‘YYYY-MM-DD’.

INSERT INTO ‘STUDENT’ (‘S_id’, ‘DoB’, ‘Gender’,’Contact_No’, City) VALUES (‘S_02’, ‘1985-12-09’, ‘Male’, ‘098987899’, Delhi);

If you are supplying the values for all of the existing columns of the table then here you can skip the query part in which you have specified the name of each column. This insertion is being done from the user but table data can also be imported from another table. In that case, how the values will be inserted is discussed below.

How to insert values from another table in SQL?

INSERT command of SQL is used to insert data into a table, this data can also be sourced from another table. Here, the syntax for this query will be like:

INSERT INTO table_name SELECT * FROM source_table;

Here let us suppose that we have two tables one is Student and other is Source_Data, then the below-listed command will be used to insert data to the Student table from Source_Data table:

Read:   How Online Training is Better Than In-Person Training?

INSERT INTO ‘Student’ SELECT * FROM ‘Source_Data’;

Here, when the above-listed query will be executed then all values from the table Source_Data will be inserted to Student table. In order to execute the above query table structure of both the tables that is Student and Source_Data must be the same. In case if the table structure is different for both the tables then you will have to specify column names in this query that will insert the corresponding values from one table to another.

What are the uses of SQL Insert Command?

INSERT query statement of SQL can perform below-listed operations:

  • INSERT INTO statement is used to insert new values in the table of a database system
  • New records or rows can be inserted into existing or new table through this statement
  • The values for some of the specific or for all columns can be inserted through this statement
  • INSERT INTO statement can also be combined with the SELECT statement.
  • You can also use WHERE clause to insert the values in a table.
  • Numeric value insertion need not have a single quote
  • String or Date values must be enclosed within a single quote
  • The values in a table can also be inserted from another table in a particular table for that you may have to merge two or more commands

In this way, you can use the SQL Insert command to add new values in the database tables. This command is mostly used alone, but scripts can also be used to use this command or SQL statement.

Final Words:

In this blog, we have discussed the SQL Insert command, there are many other DDL and DML statements in SQL that can be used to maintain and handle the relational database. You can use the database as per your requirement and for that, you need not use them separately. Even you can use them on existing and new tables as per requirement.

Here, we have discussed the command with example and the use of command is not limited, you can use it with many tables. Data insertion is one of the basic requirements for any SQL table you can use them to add new rows or values to the table.

JanBask Training

JanBask Training

JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.



Write a Comment

avatar
  Subscribe  
Notify of

Trending

Top 30 Core Java Interview Questions and Answers for Fresher, Experienced Developer
Top 30 Manual Testing Interview Questions & Answers for Fresher
Top 20 AWS Interview Question and Answers For Fresher, Experienced Developer
Spring MVC Interview Questions and Answers
Top 30 Frequently asked Selenium Interview Questions and Answers

Related Posts

How to Create Database in Microsoft SQL Server?
SQL Server Developer & Database Administrator Salary Structure
Online SQL Queries for Practice Questions with Answers
Top 50 SAS Interview Questions and Answers For Fresher, Experienced
Different Types of SQL Keys: Example and Uses