2

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?

Kristo
  • 1,339
  • 12
  • 22
  • 1
    Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. Many database systems have some kind of `identity` or auto-increment column type - but that's **highly vendor-specific** – marc_s Dec 30 '15 at 08:42
  • If you set `ID` as `indentity` (for sql server, for instance). But you should tell us your DBMS. – HoneyBadger Dec 30 '15 at 08:43
  • hmm I have set the ID to auto_increment I just need to know whats the way to insert multiple rows with the same data. What I have now is 1 row inserted with the quantity column to whatever number i enter. – Kristo Dec 30 '15 at 08:45
  • Then why did you tag it mysql? – HoneyBadger Dec 30 '15 at 08:46
  • 2
    Possible duplicate of [Best way to do multi-row insert in Oracle?](http://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle) – Elad Dec 30 '15 at 08:49

5 Answers5

2

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 );`
pri
  • 1,521
  • 2
  • 13
  • 26
1

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() )
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
1

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0
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 );
rks
  • 9
  • 4
-1

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);
rks
  • 9
  • 4
  • This won't insert multiple rows brother, I have got the same insert statement. if the txtQuantity is 50 it will just insert one row with the quantity column having a 50 value. What I need is 50 rows with the quantity of 1. – Kristo Dec 30 '15 at 08:59