Re: inserting data from a file

On Fri, 13 May 2005 16:56:49 +0200, Vince <vincent@<remove>.> wrote:

>On Tue, 10 May 2005 16:15:03 GMT, "Preston Kemp"
><dontwantany_at_nowhere.invalid> wrote:
>
>>lbunet_at_yahoo.com wrote:
>>
>>>
>>> Vince wrote:
>>> > Hello there!
>>> >
>>> > I would like to mass load data from file to table..
>>> > is there any way such as 'bulk insert' in sqlServer ?
>>> >
>>> > thanks a lot :)
>>> > ++
>>> > Vince
>>>
>>> exec master..xp_cmdshell 'bcp {database}..{table} in
>>> c:\{File_w_Data}-c -S{ServerName} -U{Username} -P{User's password}'
>>>
>>> You can also user BULK INSERT (See BOL for syntax).
>>>
>>> In the future, please post to the appropriate group
>>> (comp.databases.ms-sqlserver).
>>
>>I think he did post to the appropriate group - I read it as 'is there
>>an equivalent to SQL Server's bulk insert command in Oracle'.
>
>yes...sorry if my english is a bit poor..
>But, yes, I wondered if there is an equivalent of 'bulk insert' of
>SQLServer, I mean, not an external program that we run such as
>sql*loader but a sql statement for oracle to load data into table from
>files.
>
>thanks.
>Vince.

Hi Vince,

Yes there is : external tables. Requires at least 9.0. Syntax like this :

create table test (c1 number,c2 number,c3 varchar2(100)) organizational external
type oracle_loader
default directory mydir
access parameters (
records delimited by "\n"
fields terminated by ";")
location ('test.txt')
);

Check the docs. for further details.

• Kenneth Koenraadt