How do I do multiple INSERT INTO's...??? 1

[WildWest]

Hi gurus!!!! I want to be able to do multiple INSERT INTO's in one execution of SQL. How do I do it?

INSERT INTO MYTABLE (CCN, PRJ) VALUES ('100', '5102566')
INSERT INTO MYTABLE (CCN, PRJ) VALUES ('100', '1000238')

Please advise. Thanks in advance!!!!

 

[maxpower1]

are you writing pl/sql? use loop structure.

if not, write a script, i.e. test.sql, put these two lines in the file:-

INSERT INTO MYTABLE (CCN, PRJ) VALUES ('100', '5102566');
INSERT INTO MYTABLE (CCN, PRJ) VALUES ('100', '1000238');

commit;


then run the script at sql command prompt:-

sql>@test



~za~
You can't bring back a dead thread!

 

[carp]

In short, you don't. Each INSERT only inserts one row (unless you are inserting values using a subquery).

 

[WildWest]

I am using TOAD and I get an ORA-00911 error on the first semicolon.

I tried the following...

INSERT INTO MYTABLE (CCN, PRJ) VALUES ('100', '5102566');
INSERT INTO MYTABLE (CCN, PRJ) VALUES ('100', '1000238');
commit;

I'm not able to execute it as a filename.sql script file because I'm using a Web server via ColdFusion to execute the SQL Statement that are programmatically generated.

Please help if you can. Thanks!!!

 

[sem]

If you do need 1 SQL STATEMENT, you may use this one:

INSERT INTO MYTABLE (CCN, PRJ)
select '100', '5102566' from dual
union
select '100', '1000238' from dual


Regards, Dima

 

[dklloyd]

You may need to put a carriage return in-between the two statements to run them using the "Run the current statement" icon. It's a TOAD thing. Otherwise in TOAD you can run both using the two arrows icon (one above the other) which will run everthing in your window as a script.

Hope its of some help.

David

 

[WildWest]

Thanks sem! That's what I was looking for!!!