RE: sql query help please
- From: "Eric Hutchinson" <EricHutchinson@xxxxxxxxxxx>
- To: <sfaroult@xxxxxxxxxxxx>, <michaeljmoore@xxxxxxxxx>
- Date: Sat, 30 Apr 2011 19:48:41 -0700
Try this reference:
<http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9
014.htm#i2125362>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_90
14.htm#i2125362
You can set up your external file as an external table, and do the whole
insert in a single statement, one pass through the table.
- Eric Hutchinson
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stephane Faroult
Sent: Thursday, April 28, 2011 1:00 PM
To: michaeljmoore@xxxxxxxxx
Cc: kennethnaim@xxxxxxxxx; eugene.pipko@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: sql query help please
No, no need for an intermediate table. The multi-table insert (inserting
multiple times into the same table, that is) probably does the job.
Alternatively
insert into tableB
select div,
cust,
case n
when 1 then jan
when 2 then feb
when 3 then mar
end
from external table
cross join (select 1 n from dual
union all
select 2 from dual
union all
select 3 from dual)
HTH
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on <http://www.youtube.com/user/roughsealtd> Youtube
On 04/28/2011 09:07 PM, Michael Moore wrote:
first load it into a table (a) just as it is currently formated. Then
create a table (b) with the new format and load from a to b as follows.
insert into tableB
(select div, cust, jan from tableA
union all
select div, cust, feb from tableA
union all
select div, cust, mar from tableA) ;
Mike
On Thu, Apr 28, 2011 at 11:45 AM, Kenneth Naim <kennethnaim@xxxxxxxxx>
wrote:
Look at multi table inserts. It'll work perfectly for what you need.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Eugene Pipko
Sent: Thursday, April 28, 2011 2:42 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: sql query help please
Hi all,
Oracle 9i on Win2K3.
I am loading data from external file and need to convert it into different
format.
Current format:
--------------------
DIV CUST JAN FEB MAR
New format:
--------------------
DIV CUST JAN
DIV CUST FEB
DIV CUST MAR
Could you please point me to a reference from where I can learn how to do
it?
Thanks,
Eugene
_____
Checked by AVG - www.avg.com
Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date: 04/28/11
_____
Checked by AVG - www.avg.com
Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date: 04/28/11
Other related posts:
