INSERTING DATA INTO A TABLE

Creation a new Table:


SYNTAX:
create table tablename(columnname
datatype(size),columnname datatype(size)....);

EXAMPLE:
create table client_master(client_no
number(5),name varchar(20),address varchar
(20),city varchar(20));

Ways To Insert:

  • It has three types to insert the rows to the table.
  • Inserting a single row of data into a table.
  • Inserting data into a table from another table.
  • Inserting of selected data into a table from another table.


Inserting  Single Row:


Syntax:
insert into tablename
[(columnname,columnname)]values
(expression,expression);

Example:

insert into client_master
(client_no,name,address,city)values
(001,’abi’,’7,northstreet’,’coimbatore’);

Insert into client_master
(client_no,name,address,city)values
(002,’arun’,’16,nehrustreet’,’chennai);

insert into client_master values
(003,’jai’,’6,annanagar’,’bangalore’);

Insert into client_master values
(004,’kavita’,’8,rajastreet’,’salem’);


Select * from clientmaster;



Inserting Data From Another Table:

Syntax:

INSERT INTO tablename           
SELECT columnname,columnname,FROM
tablename;

Example:

Create table product_master(pro_no number
(10),description varchar(10),qty_on_hand number
(5),sell_price number(20),cost_price number(20));

Insert into product_master values(54,’excellent’,
10,50000,40000);

Insert into product_master values(37,’good’,
7,10000,8000);

Code:

INSERT INTO product_master SELECT
client_no,name,address,city FROM client_master;

INsertion Of Selected Data From Another Table:

Syntax:

INSERT INTO tablename SELECT
columnname,columnname FROM tablename
WHERE column=expression;

Example:

INSERT INTO product_master SELECT
client_no,name,address,city FROM client_master
WHERE client_no=‘001’;

Select * from clientmaster;




Possibility Of Errors:

Insert into client_master
(client_no,name,address,city)values
(001,’abi’,’coimbatore’);
ERROR: too many values.

Insert into client_master
(001,’abi’,’7,northstreet,’coimbatore’);
ERROR:missing select keyword

Insert into client_master values
(65487220,’abi’,’7,northstreet’,’coimbatore’);
ERROR:value larger than specified precision
allows for this column.


Thank you,
R.Rajkumar.


Like it on Facebook, Tweet it or share this article on other bookmarking websites.

No comments