3 Replies - 1727 Views - Last Post: 24 May 2015 - 02:36 PM

#1 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 484
  • Joined: 22-September 10

migrate bulk insert from sql server to oracle

Posted 28 April 2015 - 12:43 AM

i have to migrate from sql server to oracle database i am obligated to change my bulk query which i use for lot of delimeted csf files. For almost all of various of them i use this below bulk query in sql server. Does anyone know what is equivalent query in oracle database? :
ALTER PROCEDURE [dbo].[Bulk]
    @FileName NVarchar(128),
    @table NVarchar(400)
AS
BEGIN
declare @query NVarchar(max)
    set @Query =N'BULK INSERT ' + N'.' + 
      QUOTENAME(@table) + N' FROM '+ QUOTENAME(@FileName , '''')
      + N' WITH ( DATAFILETYPE =  ''char'', FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'; 
--print @query
exec (@query)
END



Is This A Good Question/Topic? 0
  • +

Replies To: migrate bulk insert from sql server to oracle

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6995
  • View blog
  • Posts: 14,630
  • Joined: 16-October 07

Re: migrate bulk insert from sql server to oracle

Posted 28 April 2015 - 06:01 AM

There's no real equivalent to the MS SQL external file load from database call.

Oracle uses something called SQL*Loader. This is similar to Microsoft's bcp.

If you're looking to migrate data from MS SQL to Oracle, I'd start with the SQL Management Studio export tools. Using OLEDB connections you can push the data right over and even create the table structure. The table structure mightn't be ideal, but it will give you a starting point.

If you already have tables, the export tools will still work. You can also use database links to push. MS SQL is far more flexible for the links, as it essentially becomes an OLE DB client. Getting Oracle to talk to SQL Server is far more problematic.
Was This Post Helpful? 0
  • +
  • -

#3 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 484
  • Joined: 22-September 10

Re: migrate bulk insert from sql server to oracle

Posted 28 April 2015 - 08:34 AM

I dont want to migrate data from sql server database. What i need is to replace actuall code loader but now for oracle. SQl server bul is good because i am not obligated to defife config file like is within sql loader tool. My vb.net application is gathering a lot of .csv delimeted files with diffrent count of columns etc. Sql server bulk is independent of that but if i would go sql loader then i would need to define for each file its own config and thats what i would like toa avoid.
Was This Post Helpful? 0
  • +
  • -

#4 wannabe21  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 24-May 15

Re: migrate bulk insert from sql server to oracle

Posted 24 May 2015 - 02:36 PM

Is this what you're looking for?

http://www.oracle.co...odp-093600.html

This uses an Oracle stored procedure (well, a package procedure) to bulk insert from associative arrays (Oracle collection objects). In PL/SQL you use the FORALL...INSERT command.

That's one way. There are few other ways to accomplish bulk inserts for Oracle..

One using array binding (no stored procedures required) ... http://www.oracle.co...net-085168.html

Another using the BulkCopy class... http://www.sqlteam.c...t-to-sql-server

I just implemented BulkCopy in a small personal project and reduced my total insert time from over 10 minutes (one row at a time) to about 8 seconds (using BulkCopy to insert).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1