Home » SQL & PL/SQL » SQL & PL/SQL » How to insert multiple records in single statement (windows oracle9i)
How to insert multiple records in single statement [message #380957] Wed, 14 January 2009 06:52 Go to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi all

i have table like temp1 which have coulumns like col1,col2
i want inert below data into the table with single statement
a,b
c,d
e,f
g,h
i,k
m,n

could You please let me know any ideas regarding this

thanks
SAIC

Re: How to insert multiple records in single statement [message #380958 is a reply to message #380957] Wed, 14 January 2009 06:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
INSERT ALL might be what you are looking for.
Re: How to insert multiple records in single statement [message #380960 is a reply to message #380957] Wed, 14 January 2009 07:02 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
But i have neraly 25000 records, for this i need to write a insert sts for each record?

any other way to solve this prob?
Re: How to insert multiple records in single statement [message #380961 is a reply to message #380960] Wed, 14 January 2009 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you want to give Oracle the values without typing them?
Yes you have to write them all either with "INSERT ALL" statement or with N "INSERT VALUES" statements (or both).

If the values are in a file, you can use SQL*Loader.

Regards
Michel

[Updated on: Wed, 14 January 2009 07:11]

Report message to a moderator

Re: How to insert multiple records in single statement [message #380962 is a reply to message #380960] Wed, 14 January 2009 07:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've got your data in a text file, you could use an external table, or Utl_File, or SQL*Loader to import the data.

Of course it'd probably be quicker to use a text editor to add the text 'INSERT INTO TABLE <table> (<list of columns) VALUES (' before each line, and add a ');' at the end of the line.

You'd still need to wrap each string field in quotes, but that's pretty straight forwards too.
Re: How to insert multiple records in single statement [message #380963 is a reply to message #380957] Wed, 14 January 2009 07:17 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member

According our JRowbottom

My idea is Like we can give separator at the end of the each recod. and make one text file for this

and we can write a some unix script (with insert sts) to load the file into tables.

thanks
Saic
Re: How to insert multiple records in single statement [message #380966 is a reply to message #380963] Wed, 14 January 2009 07:49 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, your INITIAL post should have had the information about number of records; in that case, INSERT ALL would probably not be an option.

As these records are stored in a text file, I'd probably go with SQL*Loader (from my point of view, it is the simplest solution).
Previous Topic: DBMS job
Next Topic: ora-01440
Goto Forum:
  


Current Time: Fri Dec 09 10:06:49 CST 2016

Total time taken to generate the page: 0.06056 seconds