Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL -- insert multiple rows into a table with one statement?

Tags:

sql

insert

oracle

I'd like to do insert N rows that are all identical, except one of the values is different. Specifically, this is what I am trying:

insert into attribute_list (id,value,name)
values 
(
select (id,'Y','is_leveled') from value_list where val >= 50
);

So for every value that has a val >= 50, I would insert one row into attribute_list. Can this be done with one insert statement or should I just manually generate these inserts in excel?

(note: this is a cooked example simplified to clarify the issue, so no need to attack the needlessness of this specific case)

like image 497
Jeremy Avatar asked Apr 17 '12 19:04

Jeremy


People also ask

How do you insert multiple rows using single statement?

Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement. To do this, you can list the values for each row separated by commas, following the VALUES clause of the statement.

How do you insert multiple records using a single command in SQL?

INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.

Can you insert multiple rows in Oracle?

The Oracle INSERT ALL statement is used to add multiple rows with a single INSERT statement. The rows can be inserted into one table or multiple tables using only one SQL command.

How many rows can you insert a table with one statement?

It is actually documented here: The maximum number of rows that can be inserted in a single INSERT statement is 1000.


2 Answers

You can absolutely do this in a single statement!

Try this:

INSERT INTO attribute_list (id, value, name)
SELECT id, 'Y', 'is_leveled'
FROM value_list WHERE val >= 50
like image 162
mwigdahl Avatar answered Oct 26 '22 05:10

mwigdahl


That is what FOR loops are for.

DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..10 LOOP
      IF MOD(i,2) = 0 THEN     -- i is even
         INSERT INTO temp VALUES (i, x, 'i is even');
      ELSE
         INSERT INTO temp VALUES (i, x, 'i is odd');
      END IF;
      x := x + 100;
   END LOOP;
   COMMIT;
END;
like image 43
nes1983 Avatar answered Oct 26 '22 07:10

nes1983