moving data from Oracle to SQL server

Question

moving data from Oracle to SQL server

vadepu

Newbie

Points: 1
More actions
November 25, 2002 at 11:54 am

#81254

Whats the best method of moving data from Oracle to SQL server? say on a regular basis. I am new to SQL Server , I want to know what my options are?
thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dj_meier SSCrazy Points: 2732 More actions · Answer 1

You could create a stored proc in SQL Server that uses a linked server to your Oracle server to pull the data across. This proc could be scheduled to run every so often, or you could just run in proc in a loop using a WAITFOR DELAY command to create the pause. Something like this:

 

CREATE PROC pro_MoveData

AS

  WHILE (1=1)

  BEGIN

    INSERT LocalDB.dbo.MSSQLTable1(field1,field2)

      SELECT x1, x2 FROM ORACLE1.DB1.dbo.OracleTbl1

      -- OR Use OPENQUERY

      -- SELECT x1, x2 FROM OPENQUERY(ORACLE1,'SELECT * FROM DB1.dbo.OracleTbl1')

    WAITFOR DELAY '000:10:00'  -- 10 Mins

    -- WAITFOR TIME '23:00' -- OR you could wait for a specific time

  END

RETURN

-Dan

dj_meier SSCrazy Points: 2732 More actions · Answer 2

Also, you could use a scheduled DTS package to accomplish this.

-Dan

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Depends, I generally use DTS but sometimes I have situations where a quick openquewry does great. If doing on a regular basis you need to determine first how often and what you need to move. Then I would start with DTS to figure a simple solution to meet those needs. Later, practice and test with openquery until you are comfortable and consider moving small simple items into procedures or simple jobs with INSERT steps. Also, if your Oraqcle folks are willing there is a way Oracle can push the data but we never implemented it and I never tested so not sure how well it truely works, their replication type process had a lot of issues hanging with Oracle to Oracle pushes.