INSERT data into table in Oracle.

Populating(INSERT) data into database table in Oracle.

1. Insert data into all columns(complete record) in a table.  

2. Insert data into a table in same/random order of column.
3. Insert data into selective columns of a table.
4. Inserting NULL value into table.
5. Prompt the column to insert values in runtime using ‘&’ and ‘&&’.
6. Insert data using select statement.
7. Create table as select.(create table with/without data).
8. Insert huge amount of dummy data using a PL/SQL block.
— Create a table.
CREATE TABLE emp
(EmpID   NUMBER(4)     PRIMARY KEY,
 EName   VARCHAR2(20)  NOT NULL,
 Job     VARCHAR2(20),
 DeptNo  NUMBER(3),
 Sal     NUMBER(7,2));
Insert data into all columns(complete record) in a table. 
same order of column:
INSERT INTO emp VALUES(111, ‘SCOTT’, ‘MANAGER’, 10, 50000);
INSERT INTO emp(EmpID, EName, Job, DeptNo, Sal) VALUES(112, ‘JONES’, ‘DESIGNER’, 20, 40000);
Random order of column values:
INSERT INTO emp VALUES(113, 45000, 10, ‘SMITH’, ‘MANAGER’); — show error,   — why ??
INSERT INTO emp(EmpID, Sal , DeptNo, EName, Job) VALUES(113, 45000, 10, ‘SMITH’, ‘MANAGER’);
Insert data into selective columns of a table.
INSERT INTO emp VALUES(122, 10, ‘SMITH’);  — show error 
INSERT INTO emp(EmpID, DeptNo, EName) VALUES(222, 10, ‘SMITH’);
Inserting NULL value into table.
INSERT INTO emp VALUES(214, ‘JONES’, NULL, NULL, 40000);
INSERT INTO emp(EmpID, EName, Sal) VALUES(215, ‘ADEMS’, 40000);
Prompt the column to insert values in runtime using ‘&’ and ‘&&’.
INSERT INTO emp VALUES(&empid, ‘&ename’, ‘&job’, &deptno, &sal);
INSERT INTO emp VALUES(&empid, &ename, &job, &deptno, &sal);
INSERT INTO emp(EmpID, EName, Job, DeptNo, Sal) VALUES (&empid, ‘&ename’, ‘&job’, &deptno, &sal);
INSERT INTO emp(EmpID, DeptNo, EName) VALUES (&empid, &deptno, ‘&ename’);
INSERT INTO emp(EmpID, DeptNo, EName, Sal) VALUES (&empid, &&deptno, ‘&ename’, &&sal);
Insert data using select statement.
INSERT INTO emp (SELECT 222, ‘SATISH’, ‘MANAGER’, 30, 60000 FROM DUAL);
INSERT INTO emp VALUES(SELECT 223, ‘RAHUL’, ‘SALESMAN’, 30, 30000 FROM DUAL);   — show error.
Create table as select.(create table with/without data).
CREATE TABLE emp_copy AS SELECT * FROM emp; — create table with same table structure, with data.
SELECT * FROM tab;
SELECT * FROM emp_copy;
CREATE TABLE emp_copy2 AS SELECT * FROM emp where 1<>1; 
— create table with same table structure, without data.
SELECT * FROM tab;
SELECT * FROM emp_copy2;
Insert huge amount of dummy data using a PL/SQL block.
BEGIN
 FOR i IN 1..100 LOOP
  INSERT INTO emp VALUES(i, ‘Ename_’||i, ‘Job_’||i, (MOD(i,5)+1)*10, (MOD(i,5)+1)*10000);
 END LOOP;
END;
/
SELECT * FROM emp;

For more details on this topic visit the link : https://youtu.be/B6W9Xr_asxU

1 thought on “INSERT data into table in Oracle.”

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn