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

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

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

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 Example

INSERT INTO sample2 SELECT * FROM sample1;

O/P

select * from sample2;

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;

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 Only those rows where age  is 20 from sample1 are copied into the sample2 table