CODEWITHSUNDEEP
oracle11g
Ruchita P
Ruchita P

Reputation: 389

Insert constant values in oracle

How to insert constant values in single statement in oracle ?

Similar, to the one we can achieve in MSSQL Server..

CREATE TABLE grade
(
  names varchar(1),
  grade int
)
-- insert data
INSERT INTO grade
VALUES ('A',100),('B',90),('C',80),('D',70),('E',70),('F',60)

Thank you

Upvotes: 1

Views: 804

Answers (1)

MT0
MT0

Reputation: 168741

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE grade
(
  names varchar2(1),
  grade int
);

Just use a normal INSERT statement:

INSERT INTO grade ( names, grade )
  SELECT 'A',100 FROM DUAL UNION ALL
  SELECT 'B', 90 FROM DUAL UNION ALL
  SELECT 'C', 80 FROM DUAL UNION ALL
  SELECT 'D', 70 FROM DUAL UNION ALL
  SELECT 'E', 60 FROM DUAL UNION ALL
  SELECT 'F', 50 FROM DUAL;

Or:

INSERT INTO grade ( names, grade ) VALUES ( 'A', 100 );
INSERT INTO grade ( names, grade ) VALUES ( 'B',  90 );
INSERT INTO grade ( names, grade ) VALUES ( 'C',  80 );
INSERT INTO grade ( names, grade ) VALUES ( 'D',  70 );
INSERT INTO grade ( names, grade ) VALUES ( 'E',  60 );
INSERT INTO grade ( names, grade ) VALUES ( 'F',  50 );

If you want them to be unmodifiable then you can set the table to be READ ONLY:

ALTER TABLE grade READ ONLY;

Query 1:

SELECT * FROM grade

Results:

| NAMES | GRADE |
|-------|-------|
|     A |   100 |
|     B |    90 |
|     C |    80 |
|     D |    70 |
|     E |    60 |
|     F |    50 |

Upvotes: 1

Related Questions