Oracle for DBA

Dedicated to my 'True Teacher' Apurva Jadhav (Madam).

Wednesday, 13 October 2010 06:51:50 GMT

Inserting row(s) into table using SUBQUERY.

Inserting row(s) into table USING SUBQUERY.

Insert is a (DML) Data manipulation Language. Used to inserting row(s) to the existing table. It implicitly commit changes to database.

Syntax:-

INSERT INTO table_name (SUBQUERY);

EX:-day before yesterday we had created BOOK table. Today we will insert row to that table using SUBQUERY.

CREATE TABLE book
(
sno NUMBER,
title VARCHAR2(10),
published DATE
);

INSERT INTO book (SELECT 4,USER,SYSDATE FROM DUAL);

Where
  • 'INSERT INTO' is oracle reserved word for insertion.
  • 'book' is the name of a table.
  • '(SELECT 4,USER,SYSDATE FROM DUAL)' are values for a column seprated by comma enclosed in parenthesis.
Where

  • "SELECT" is a oracle reserved word, to select data.
  • "4" is a value for SNO column.
  • "USER" is a value for TITLE column. It is a oracle reserved word, show the current user_name connected to database.
  • "SYSDATE" is a value for PUBLISHED column. It is a oracle reserved word, show the current system_date
  • "FROM" is a oracle reserved word, used to find data from table.
  • "DUAL" is a table consists of one column name DUMMY having VARCHAR2(1) datatype and value X. it is used to operate literal, numeric and date value for single row operation.
";" semicolon is used to execute a command.

NOTE:- keyword VALUES never comes in SUBQUERY insertion.

---------------------------------------------------

If your query is OK you will get message " 1 row created ".

---------------------------------------------------

You can view your inserted row(s) in table by using:

SELECT * FROM table_name;

---------------------------------------------------
if you want to know any command or explanation let me know at: oracle.ohlog@yahoo.in



In: Uncategorised
Permalink : Inserting row(s) into table using SUBQUERY.
Comments: 0
Viewed 496 times.





Make a comment

*
Smilies
*
Enter the code shown. 

Praise to Lord Jesus.