SQL Server Performance Forum – Threads Archive
DTS Newbie – Loading A lot Oracle Data
Hi, I want to copy a subset of Oracle data to Sql Server 2000 staging table.There are about 22Million Rows in this table.
I have a sql query to select the columns I want but the rowcount will be 20+Million. What is the best way to do this ?
I may need to do this every month to resync Oracle Data into Sql Server. 1. DTS job to pull the data from Oracle Server.
2. Unload the Oracle data to a file. Copy the file to the Sql Server
and then run DTS Task to load the file on the Sql Server 3. Use some form of Sql Scripting to do this instead of DTS …
or do DTS already has a script that I can export and use as a sql script. Thanks, Paul
I would use a DTS package. SQL can connect direct to Oracle and pull the data. Though if it’s over a WAN you may consider exporting to a text file first and then importing to SQL. You could then compress the data over the link. Simon
I think it is faster if you import data from text file. So do export data to text file from Oracle and import it from SQL Server Madhivanan Failing to plan is Planning to fail
Probably another way would be to create a linked server. See if this helps:http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Hi again, Ok, thanks for all the replies. 1. DTS option
I finally decided to use DTS with fetch buffer = 1000 and Batch = 5000 (commit rows).
This copied the 20+ million in 10 hrs over our WAN overnight (acceptable – not 20 hrs ).
I was concerned about TEMPDB and storage on the target Sql Server, but its fine. 2.Export to a File
The Oracle Group would not provide this option on short notice. 3. Use a Linked Server
Would a stmt such as: insert …. into MySqlTable
select ….. from openquery(MyOracle, ‘select … from oracletable where … ‘) Would there be issues with tempdb filling up with 20 million rows ?
How would you make this query restartable ? Thanks, Paul
You will probably find that this will time out over the defult timeout value for linked servers (which is 10 mins). You can change this value in the linked server properties though. I would strongly recommend batches rather than one statement. Something like: DECLARE @@ROWCOUNT int
SET @RowCount = 1
WHILE @RowCount > 0
BEGIN
INSERT INTO SQLTable SELECT TOP 1000 * FROM OracleTable WHERE ID > (SELECT MAX(ID) FROM SQLTable)
SET @RowCount = @@ROWCOUNT
END Though personally I would go the DTS option – just because it is easier to control batch sizes and if possible I would select TABLOCK on the table being inserted into (though you can still do this in the query above using WITH). As for your comment about the tempdb it going to be down to disk space. Preferably the tempdb would be on it’s own disk too. Obviously this could have a negative impact on other server load whilst its running. Simon
Hi, Simon’s comment: Ok, good points: 1. I should lock the target table using WITH TABLOCK. 2. Separate Hard Drive for TempDB:
"Preferably the tempdb would be on it’s own disk too. Obviously this could have a negative impact on other server load whilst its running". On our Test Server, the TempDB is on the same physical Hard Drive as the Data Files. What is the best way to move the TempDB db to another HardDrive ? Stop Test Sql Server
Copy TempDB to hard drive
Start Sql Server
Change location to point to new harddrive
Delete old TempDB db. Well, I beleve in Production Sql Server we will be using RAID 5 DiskArray, then is location of TEMPDB still an issue ? Many Thanks, Paul
]]>