INSERT Query in DBMS

INSERT Query in DBMS

 

In this article, we will learn about INSERT Query in DBMS.

A database is not permanent, User can make any changes to the database table by adding, deleting, updating existing records by using DML (data manipulation language ) command.

  • INSERT  is a widely used data manipulation language(DML)command for adding new data to the existing database table
  • Insert command is used to add one or more rows of data  to the database table with specified  column values

Syntax for INSERT

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Consider a sample table student,  initially as empty.

INSERT Query in DBMS

Simple INSERT Query in DBMS

  • In this type of insert, all the values should be provided to all the columns that exist in the table without specifying the column name
  • The order of values declared in the values clause should follow the original order of the columns in the table

Example

insert into student
 values(66'trishaank','computers','24-07-1998',92);

O/P

1 row added
stuidsnamebranchdobscore
66Trishaankcomputers24-07-199892

Inserting data into required columns

In this case, the order of columns declared in insert need not be the same as that of the original table order, only the specified column values are added and null values added to the remaining column values in that tuple

Example

insert into student(stuid,sname,branch) values (82,'Srinivas','Computers');

O/P

Number of Records: 2
stuidsnamebranchdobscore
66Trishaankcomputers24-07-199892
82Srinivascomputersnullnull

In this case, the row added, values are inserted only for studid,sname, and branch, whereas for dob and score null values for inserted.

 

Dynamic INSERT Query in DBMS (using ampersand &)

In this type of insert, the values are entered by the user at the execution time

Example

insert into student values(&stuid,'&sname','&branch','&dob',&score);

Action 

enter value for stuid:73
enter value for sname:Prashanth
enter value for brance:Physics
enter value for dob:17-08-1997
enter value for score:78

O/P

Number of Records: 3
stuidsnamebranchdobscore
66Trishaankcomputers24-07-199892
82Srinivascomputersnullnull
73PrashanthPhysics17-08-199778

 

Using select in the INSERT  command

Copying all columns of a table

  • We can copy the data(rows) from one table and insert into another table by using this combination of select and insert
  • Consider the table sample1 with following data and sample2 which is empty as of now with the same structure as sample1, Now we can copy  all the rows present in the sample1 table to sample2 table

Sample 1 table

Number of Records: 5
name  age 
trish20
prash21
sanju22
srinu21
Chandana20

Example

INSERT INTO sample2 SELECT * FROM sample1;

O/P

select * from sample2;
Number of Records: 5
nameage
trish20
prash21
Sanju22
srinu21
Chandana20

Copying specific columns of a table

You can also copy only required columns from one table to another table using insert into and select combination

INSERT INTO sample2(name) SELECT name FROM sample2;

O/P

select * from sample2;
Number of Records: 5
nameage
trishnull
prashnull
Sanjunull
srinunull
Chandananull

Here only name column present in sample1 gets copied into sample2

Copying specific rows from a table

You can add only specific records by filtering the rows based on the condition specified in the where clause

INSERT INTO sample2 SELECT * FROM sample1 
WHERE age=20;

O/P

select * from sample2

Number of Records: 2

nameage
trish20
Chandana20

Only those rows where age  is 20 from sample1 are copied into the sample2 table