Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sbaroux
Starting Member
9 Posts |
 Posted - 2004-09-16 : 05:17:03
|
| Hello,I need to transfer some big tables from SQL server 2000 to oracle (several million records).I've tried to do it via DTS. It seems to work but it's very very slow.I've tried via text file, but I've got some error on date fields and numeric fields (separator "." in text file and ',' expected in oracle)Do you have any suggestion to manage this?ThanksStephanie |
|
|
syedrehman
Starting Member
7 Posts |
Posted - 2004-09-16 : 14:41:55
|
| Have you looked at bcp to output from SQL? Not sure what the equivalent to bcp is in Oracle. bcp allows the use of a format file, which you can use to specify the separator to be "," |
 |
|
|
naka55n
Starting Member
10 Posts |
Posted - 2004-09-16 : 15:46:22
|
| You can use "bcp" to export the data from SQL Server. Oracle includes to similar utilities "imp" to import the data and "exp" to export the data. |
|
|
|
dasu
Posting Yak Master
104 Posts |
Posted - 2004-09-17 : 00:54:32
|
| I think using bcp u can transfer data from text file to any databasebut its not for data base to another database(acording to me).regardsdasu.g |
|
|
|
dasu
Posting Yak Master
104 Posts |
Posted - 2004-09-17 : 00:54:47
|
| I think using bcp u can transfer data from text file to any databasebut its not for data base to another database(acording to me).regardsdasu.g |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-09-17 : 01:33:54
|
| I would disagree with you dasu. Here's some sample code if you need it.http://www.nigelrivett.net/MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
naka55n
Starting Member
10 Posts |
Posted - 2004-09-17 : 14:42:39
|
| In my previous post I noted two (imp and exp) Oracle utilites that would allow the data to be imported into Oracle from SQL Server. After reading my response again I realized that imp and exp are specific to the Oracle database. You need to look at the SQL*Loader utility. It will allow you to import fixed length or delimited data into an Oracle table. Sorry for my confusion. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-09-17 : 15:00:26
|
| In the other thread you started on this subject, I suggested that you try the Oracle OLE db provider rather than the Microsoft supplied Oracle provider in DTS. I have had good success with this, as the microsoft version has not had any significant changes in about 4 years.-ec |
|
|
|
|
|
|
Transfer from SQL server to oracle