5 Replies - 12491 Views - Last Post: 22 July 2011 - 08:32 PM

#1 AverageAsian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 24-January 11

SQL Developer: Inserting multiple rows with a single INSERT statement

Posted 22 July 2011 - 02:18 PM

I'm having a bit of an issue trying to get this method to work. I am, however, able to do individual rows just fine. So here are the ways I've tried doing multiple rows, I found out about these attempts from researching on the internet:

(With Commas)
INSERT INTO ENROLLMENT
 (STUDENT_ID, SECTION_ID, ENROLL_DATE, FINAL_GRADE, 
  CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
VALUES
 (375, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE),
 (137, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE),
 (266, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE),
 (382, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE);



(With UNION ALL)
INSERT INTO ENROLLMENT
 (STUDENT_ID, SECTION_ID, ENROLL_DATE, FINAL_GRADE, 
  CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
VALUES
 (375, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE),
 (137, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE),
 (266, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE),
 (382, 48, TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE);



Unfortunately, they all failed. I get a "ORA-00933: SQL command not properly ended" error message.
Any kind of methods, tips, or guidance towards my problem is very much appreciated, thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Developer: Inserting multiple rows with a single INSERT statement

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: SQL Developer: Inserting multiple rows with a single INSERT statement

Posted 22 July 2011 - 02:42 PM

Why are you under the impression you CAN insert multiple rows with a single insert statement? Simply, you can't. There are a few RDBMS' that will allow this kind of syntax, but Oracle isn't one of them.

To be fair, it's just syntax sugar. Every row must be individuality inserted into a table, regardless of the compounding of the statement
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: SQL Developer: Inserting multiple rows with a single INSERT statement

Posted 22 July 2011 - 02:47 PM

Ok, I'll amend that. If you really, really, OMG, wanted to, then:
INSERT INTO ENROLLMENT(STUDENT_ID, SECTION_ID, ENROLL_DATE, FINAL_GRADE,  
		CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
	SELECT STUDENT_ID, 48, 
			TO_DATE(SYSDATE, 'DD-MON-YY'), NULL, 'MTV', SYSDATE, 'MTV', SYSDATE)
		FROM (
			SELECT 375 AS STUDENT_ID FROM DUAL
			UNION SELECT 137 AS STUDENT_ID FROM DUAL
			UNION SELECT 266 AS STUDENT_ID FROM DUAL
			UNION SELECT 382 AS STUDENT_ID FROM DUAL
			UNION SELECT 375 AS STUDENT_ID FROM DUAL
		)


Was This Post Helpful? 0
  • +
  • -

#4 AverageAsian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 24-January 11

Re: SQL Developer: Inserting multiple rows with a single INSERT statement

Posted 22 July 2011 - 05:51 PM

View Postbaavgai, on 22 July 2011 - 09:42 PM, said:

Why are you under the impression you CAN insert multiple rows with a single insert statement? Simply, you can't. There are a few RDBMS' that will allow this kind of syntax, but Oracle isn't one of them.

To be fair, it's just syntax sugar. Every row must be individuality inserted into a table, regardless of the compounding of the statement


Oh really? That's interesting. The instructions to use a single statement, maybe I interpreted it wrongly.

The code you posted is interesting as well, the DUEL table it looks like is much more useful than just testing commands. I would have never thought to use the DUAL table, maybe I should think about it more. I'm getting an error of a missing right parenthesis and I don't know where it should go. I placed one after the first SELECT but that didn't do it.

Also, I'd like to further understand the logic of what you just did there, do you mind going through it? I'm a bit slow haha
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: SQL Developer: Inserting multiple rows with a single INSERT statement

Posted 22 July 2011 - 06:57 PM

The DUAL table is essentially the dummy table of Oracle. e.g.
SELECT 'Foo' as Bar FROM DUAL;

BAR
---
Foo



Other versions of SQL don't use dual at all, but simply allow SELECT 'Foo';. The construct I offered just chains this together, so:
SELECT 'Alpha' as Nato from dual
UNION
SELECT 'Bravo' as Nato from dual
UNION
SELECT 'Charlie' as Nato from dual;

NATO
-------
Alpha
Bravo
Charlie



Since you can use the results of a select to load an insert, and you can use DUAL for a fake select, you can use the result set of a non existent table to load an insert.

Oracle documentation is pretty exhaustive. I'd recommend googling the term you're interested in with "oracle" next to it for an in depth description of anything you're interested in. Just understand that different databases have different special syntax and make sure you're reading about oracle and not MS SQL or MySql or whatever.
Was This Post Helpful? 0
  • +
  • -

#6 AverageAsian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 24-January 11

Re: SQL Developer: Inserting multiple rows with a single INSERT statement

Posted 22 July 2011 - 08:32 PM

View Postbaavgai, on 23 July 2011 - 01:57 AM, said:

The DUAL table is essentially the dummy table of Oracle. e.g.
SELECT 'Foo' as Bar FROM DUAL;

BAR
---
Foo



Other versions of SQL don't use dual at all, but simply allow SELECT 'Foo';. The construct I offered just chains this together, so:
SELECT 'Alpha' as Nato from dual
UNION
SELECT 'Bravo' as Nato from dual
UNION
SELECT 'Charlie' as Nato from dual;

NATO
-------
Alpha
Bravo
Charlie



Since you can use the results of a select to load an insert, and you can use DUAL for a fake select, you can use the result set of a non existent table to load an insert.

Oracle documentation is pretty exhaustive. I'd recommend googling the term you're interested in with "oracle" next to it for an in depth description of anything you're interested in. Just understand that different databases have different special syntax and make sure you're reading about oracle and not MS SQL or MySql or whatever.


I shall take the DUAL table more into consideration now, thanks for the tips and advice! Its amazing how complex SQL can get when you start combining statements and clauses.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1