CODEWITHSUNDEEP
sqloracle-databasesql-insert
Kristo
Kristo

Reputation: 1367

SQL insert same values with different IDs in 1 query

Is it possible to insert multiple values in a table with the same data except from the primary key (ID)?

For instance:

INSERT INTO apples (name, color, quantity) 
VALUES of(txtName, txtColor, txtQuantity)

Is it possible to insert 50 red apples with different IDs?

ID(PK) |Name  | Color | Quantity
1       apple   red      1
2       apple   red      1

Is it possible like this?

Upvotes: 2

Views: 6427

Answers (5)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could do it in single SQL statement using CONNECT BY clause, also known as Row generator method.

For example, to generate 10 rows:

SQL> SELECT LEVEL  ID,
  2        'apple' NAME ,
  3        'red'   color,
  4        1       quantity
  5  FROM dual
  6    CONNECT BY LEVEL <=10;

        ID NAME  COLOR    QUANTITY
---------- ----- ------ ----------
         1 apple red             1
         2 apple red             1
         3 apple red             1
         4 apple red             1
         5 apple red             1
         6 apple red             1
         7 apple red             1
         8 apple red             1
         9 apple red             1
        10 apple red             1

10 rows selected.

SQL>

You could use the above SELECT as INSERT INTO SELECT statement.

Upvotes: 1

rks
rks

Reputation: 9

CREATE TABLE APPLES(PK_ID NUMBER PRIMARY KEY,NAME VARCHAR2(100), COLOR VARCHAR2(100), QUANTITY VARCHAR(200));
INSERT INTO APPLES (PK_ID, NAME, COLOR, QUANTITY)  VALUES(1, 'apple', 'red', 1 );
INSERT INTO APPLES (PK_ID, NAME, COLOR, QUANTITY)  VALUES(2, 'apple', 'red', 1 );

Upvotes: 0

rks
rks

Reputation: 9

Create Table with 4 columns

then Add same set of rows with different primary key

INSERT INTO apples (ID,name, color, quantity) VALUES (UniqueID,txtName, txtColor, txtQuantity);

Upvotes: -1

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can use INSERT ALL or use the UNION ALL like this.

INSERT ALL
   INTO apples  (name, color, quantity) VALUES ('apple', 'red', '1')
   INTO apples  (name, color, quantity) VALUES ('apple', 'red', '1')
   INTO apples  (name, color, quantity) VALUES ('apple', 'red', '1')
SELECT 1 FROM DUAL;

or

insert into apples (name, color, quantity)
select 'apple', 'red', '1' from dual
union all 
select 'apple', 'red', '1' from dual

Prior to Oracle 12c you can create SEQUENCE on your ID column. Also if you are using Oracle 12c then you can make your ID column as identity

CREATE TABLE apples(ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

Also if the sequence is not important and you just need a different/unique ID then you can use

CREATE TABLE apples( ID RAW(16) DEFAULT SYS_GUID() )

Upvotes: 1

pri
pri

Reputation: 1531

You can use SEQUENCE.

`CREATE SEQUENCE seq_name
  START WITH 1
  INCREMENT BY 1`

Then in your INSERT statement, use this

`INSERT INTO apples (id, name, color, quantity)
  VALUES(seq_name.nextval, 'apple', 'red', 1 );`

Upvotes: 2

Related Questions