Home » SQL & PL/SQL » SQL & PL/SQL » Insert multiple records into a table (Oracle v9.2)
Insert multiple records into a table [message #347470] |
Thu, 11 September 2008 16:29  |
dnavarrojr
Messages: 3 Registered: September 2008 Location: Kansas, USA
|
Junior Member |
|
|
I have successfully used INSERT ALL to insert multiple records into a table from another table. However, I cannot find any form of syntax for inserting multiple records into a table from static data.
For example, if I have a table called "mytable" with a single column called "number", I cannot figure out how to insert more than one number into that table.
I can insert a single number:
INSERT INTO mytable (number) VALUES (0)
but what I want to do is insert more than one value at a time:
INSERT ALL INTO mytable (number) VALUES ({0,1,2,3})
Is this possible?
|
|
|
|
Re: Insert multiple records into a table [message #347626 is a reply to message #347490] |
Fri, 12 September 2008 07:33   |
dnavarrojr
Messages: 3 Registered: September 2008 Location: Kansas, USA
|
Junior Member |
|
|
Thanks Kevin, but that page does not have an example of inserting multiple values into the same field from a source other than another table. I had already done numerous google searches and didn't find any examples (in fact, I had already seen that page).
So, I am guessing it can't be done.
|
|
|
|
|
Re: Insert multiple records into a table [message #347647 is a reply to message #347470] |
Fri, 12 September 2008 10:02   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Thanks for doing your own original googel'ing. Can we not simply pivot your data so that it looks like rows rather than columns, and then do the typical insert all? Indeed the simple example we started with does not require insert all.
Consider this select statement driven by the original insert example:
select substr(','||'0,1,2,3'||','
,instr(','||'0,1,2,3'||',',',',1,rownum)+1
,instr(','||'0,1,2,3'||',',',',1,rownum+1)-instr(','||'0,1,2,3'||',',',',1,rownum)-1
) avalue
from dual
connect by level <= length(','||'0,1,2,3'||',')-length(replace(','||'0,1,2,3'||',',','))-1
/
Kevin
[Updated on: Fri, 12 September 2008 10:21] Report message to a moderator
|
|
|
Re: Insert multiple records into a table [message #347665 is a reply to message #347647] |
Fri, 12 September 2008 10:41  |
dnavarrojr
Messages: 3 Registered: September 2008 Location: Kansas, USA
|
Junior Member |
|
|
Many thanks for your time. I really appreciate it.
I ended up just writing a quick web app in PhP using a loop to insert the data into the table. I was just looking for something I could add to my scripts folder to do it without having to log into our web site.
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:22:25 CST 2025
|