How to insert multiple rows with a single INSERT in Oracle?

5 pts.
Tags:
Oracle
Oracle Statements
How to insert multiple rows with a single INSERT in Oracle? I don't want to write 5 INSERT statements for this to insert 5 rows. Instead I prefer to execute this in one single statement. It would be great to know the solution. Thanks Suresh

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are inserting new rows from a client side application they will need to be inserted separately.

The only way to insert more than one row at a time is if they come from another table.

Actually – depending on which version of Oracle you are using – if you are using 10g and above you can use the Insert All – see below.

INSERT ALL
INTO table (column1, column2, , ,)
VALUES (list of values)
SELECT ....
FROM table1, table2, ,
WHERE....;

In this way you can insert multiple rows of data from multiple tables into one table. You can enter some values and get values from the select statement. If you are inserting data from a spreadsheet or comma delimited file, you can either create a temporary table or an external table to load the data from.

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FrankKulash
    Hi, Suresh, The form of a multi-row INSERT is
    INSERT INTO table_name (column_1, column_2, ..., column_n)
    SELECT value_1, value_2, ..., value_n
    FROM ...
    
    If the values don't actually exist in any table, you can SELECT them as literals from any table or view, such as dual or one of the data dictionary views. (The view all_views is handy because everyone can SELECT from it, and it has hundreds of rows.) The pseudo column ROWNUM is n for the nth row selected, and you can map this to your values using CASE (as shown below) or DECODE. For example, say you want to INSERT this data into table_x:
            ID DT          NAME
    ---------- ----------- --------------------
           201 01-Jan-2008 Aardvark
           202 01-Feb-2008 Bass
           203 01-Mar-2008 Cedusa
           204 01-Apr-2008 Dodo
           205 01-May-2008 E. Coli
    
    Here's how you can INSERT it in a single SQL statement:
    INSERT INTO table_x (id, dt, name)
    SELECT	CASE	ROWNUM
    		WHEN	1	THEN	201
    		WHEN	2	THEN	202
    		WHEN	3	THEN	203
    		WHEN	4	THEN	204
    		WHEN	5	THEN	205
    	END
    ,	TO_DATE	( CASE	ROWNUM
    			WHEN	1	THEN	'01-Jan-2008'
    			WHEN	2	THEN	'01-Feb-2008'
    			WHEN	3	THEN	'01-Mar-2008'
    			WHEN	4	THEN	'01-Apr-2008'
    			WHEN	5	THEN	'01-May-2008'
    		  END
    		, 'DD-Mon-YYYY'
    		)
    ,	CASE	ROWNUM
    		WHEN	1	THEN	'Aardvark'
    		WHEN	2	THEN	'Bass'
    		WHEN	3	THEN	'Cedusa'
    		WHEN	4	THEN	'Dodo'
    		WHEN	5	THEN	'E. Coli'
    	END
    FROM	all_views
    WHERE	ROWNUM	
    1,240 pointsBadges:
    report
  • FrankKulash
    Hi, again, My previous message got truncated when I used a less-than sign. I'll try it again, using ampersand-lt-semicolon. The last line of the previous statement should read:
    WHERE	ROWNUM	<= 5;
    
    Notice that this statement is much harder to read and much longer than using five separate INSERT statements. You can decide which is better to use in your application. If there are patterns in the data (such as dt in this example, which is the firt day of five consecutive months), you can shorten the code, for instance:
    INSERT INTO table_x (id, dt, name)
    SELECT	ROWNUM + 200
    ,	ADD_MONTHS	( TO_DATE ('01-Dec-2007', 'DD-Mon-YYYY')
    			, ROWNUM
    			)
    ,	CASE	ROWNUM
    		WHEN	1	THEN	'Aardvark'
    		WHEN	2	THEN	'Bass'
    		WHEN	3	THEN	'Cedusa'
    		WHEN	4	THEN	'Dodo'
    		WHEN	5	THEN	'E. Coli'
    	END
    FROM	all_views
    WHERE	ROWNUM	<= 5;
    
    1,240 pointsBadges:
    report
  • Vinayprasanna
    in oracle 9i & is used to insert multiple values in a single table in that way is there any provision in oracle 10g for inserting multiple values in sql prompt
    15 pointsBadges:
    report
  • carlosdl
    Vinayprasanna: How is '&' used to insert multiples values on 9i ? Also, what exactly do you mean by 'multiple values' (the original question was about multiple 'rows') ?
    69,065 pointsBadges:
    report
  • Rajpavan54
    hi FrankKulash, How is ‘&’ used to insert multiples values on 10g? can u explain clear with examples.
    25 pointsBadges:
    report
  • Rajpavan54
    Answer for the above query is INSERT INTO TABLE_NAME (column_name1,column_name2,column_name3.......) values (&column_name1,&column_name2,........); Example:- create table KRP (ename varchar2(20),contact number(10)); Table create insert into krp (ename,contact) values ('&ename',&contact);
    25 pointsBadges:
    report
  • AmitBhuMca
    You can insert multiple rows using the following syntax: INSERT ALL INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3') INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3') INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3') SELECT * FROM dual; this command can insert multiple values in a single step. You can also insert multiple values in multiple tables by using this command. e.g. INSERT ALL INTO product (product_id,product_name) VALUES (1000, 'Disc') INTO product (product_id, product_name) VALUES (2000, 'Floppy') INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York') SELECT * FROM dual;
    10 pointsBadges:
    report
  • Michael Tidmarsh
    Looking for more information? See how you can insert multiple rows in Oracle 10g.
    29,480 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following