SQL Insert – Explanations and examples

This session is all about Insertion. SQL provides INSERT statement which helps to insert one or more records of data into the database table. 

Let us consider a default table to understand this concept. Assume the name of the table is “codingeek“.

NameCourseLevel
 nishiJava Beginner
faiz SQL Intermediate
riyaPython Beginner
dishy CAdvanced

INSERT INTO statement:

There are two ways of inserting records into the database table.

  • If the table structure is known, directly the values can be inserted without mentioning the column names. Note that the order should be maintained as per the example below.
  • If the table structure is not known or if you want to insert only into particular columns then give the values along with the column name.
--SYNTAX Example 1 - Inserting without mentioning the column names

INSERT INTO 
Table_name VALUES (value1,value2,value3,...valueN);

--SYNTAX Example 2 - Inserting for specific columns

INSERT INTO 
Table_name(column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

Query Parameters:

  • Table_name: table to which the records are to be inserted.
  • column1, column2: the columns in the table to insert values.
  • value1, value2: the values assigned to the columns in the table.
    Say, column1 is assigned with value1, column2 with value2 and so on.

Example – Insert without column names:

Now you know the structure of the table and you want to insert a row into the “codingeek” table. This can be done as follows,

--EXAMPLE

INSERT INTO 
codingeek VALUES ('neetu','Java','Advanced');

The above code will insert the values in the following manner which is represented as,

INSERT INTO
INSERT INTO tablename VALUES(…);

Output:

SELECT * FROM codingeek;
NameCourseLevel
 nishiJava Beginner
faiz SQL Intermediate
riyaPython Beginner
dishy CAdvanced
neetu JavaAdvanced

Example – Insert with column names:

At times you don’t remember the table structure. In that case, you can insert the column values based on the order you have defined.

INSERT INTO 
  codingeek (Level, Name, Course)
  VALUES ('Beginner','sam','DS');

Output:

SELECT * FROM codingeek;
NameCourseLevel
nishiJavaBeginner
faizSQLIntermediate
riyaPythonBeginner
dishyCAdvanced
neetuJavaAdvanced
samDSBeginner

Multiple rows can also be inserted which is done as follows:

INSERT INTO 
codingeek(Level,Course,Name)
VALUES
('Advanced','Python','smita'),
('Intermediate','C','neha');

Output:

SELECT * FROM codingeek;
NameCourseLevel
nishiJavaBeginner
faizSQLIntermediate
riyaPythonBeginner
dishyCAdvanced
neetuJavaAdvanced
samDSBeginner
smitaPythonAdvanced
nehaCIntermediate

Inserting data from another table:

We can insert records from the table to table if necessary. Remember that there should not be any mismatch of data types. The syntax for inserting values from another table is:

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2,...columnN 
   FROM second_table_name
   WHERE [condition];

Insert Specific Data from one table to another –

Assume there exist another table in the database called “tutorials”. How to insert data from tutorials to codingeek is seen as follows:

IDStudent_namePreferred_subjectDuration
 1aliaJava88 months
2 shahC++3 months
3elle Ruby6 months
INSERT INTO codingeek (Name,Course)
  SELECT Student_name, Preferred_subject
    FROM tutorials
    WHERE ID > 0;

Output:

 
SELECT * FROM codingeek; 
NameCourseLevel
nishiJavaBeginner
faizSQLIntermediate
riyaPythonBeginner
dishyCAdvanced
neetuJavaAdvanced
samDSBeginner
smitaPythonAdvanced
nehaCIntermediate
aliaJava8NULL
shahC++NULL
elleRubyNULL

Note – As you can see, if there are no values to the corresponding column when rows are taken from another table, by default it takes NULL value.


Insert all data from another table:

Consider two tables,

Table – details:

IDName
s1 sarah
s2 komal
s3 sakshi
s4sona

Table – info:

NoName
y1 anish
y2arsha
y3 yasmi

As you can see, both the tables have the same structure. If that is the case, then you can insert one table into another which can be seen as follows:

 
INSERT INTO details 
SELECT * FROM info</pre>
<pre>

Output:

IDName
s1 sarah
s2 komal
s3 sakshi
s4sona
y1anish
y2arsha
y3yasmi

The values are inserted from “info” table into “details” table as their structure match with each other. Else it would display with error.

Note You can use the above query only if the structure of the tables you want to insert data from and to matches. Otherwise the query is invalid.


The capacity to learn is a gift,the ability to learn is a skill, but the willingness to learn is a choice!!

Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂