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 Go to next message
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 #347490 is a reply to message #347470] Thu, 11 September 2008 20:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
try this, I did a simple google for oracle insert all and this popped up:


Oracle Insert Statements

Kevin
Re: Insert multiple records into a table [message #347626 is a reply to message #347490] Fri, 12 September 2008 07:33 Go to previous messageGo to next message
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 #347628 is a reply to message #347626] Fri, 12 September 2008 07:39 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Search for CONNECT BY.
Re: Insert multiple records into a table [message #347630 is a reply to message #347626] Fri, 12 September 2008 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Post what you tried.

Regards
Michel
Re: Insert multiple records into a table [message #347647 is a reply to message #347470] Fri, 12 September 2008 10:02 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: value from Procedure
Next Topic: Update Statement
Goto Forum:
  


Current Time: Sat Feb 15 02:22:25 CST 2025