Copy Oracle table to SQL Server 2000 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Copy Oracle table to SQL Server 2000

I have 4 tables that I need to import from Oracle. I’ve used DTS to import the raw data from my other tables, but DTS won’t work on these 4 because they have the CLOB datatype. Does anyone know a way to export a table that has a CLOB field from Oracle to SQL Server 2000? ———-
T Kelley
When creating tables in SQL Server data types are subtly different. VARCHAR2 should be replaced by VARCHAR
NUMBER should be replaced by DECIMAL (or NUMERIC)
NUMBER columns with no digits after the decimal point can be converted to INT (or BIGINT)
DATE should be replaced by DATETIME
SQL Server has a MONEY (and associated SMALLMONEY) data type, Oracle doesn’t. Use at your own risk.
CLOB should be replaced by TEXT
BLOB should be replaced by IMAGE May the best cheaters win
I found the datatype connection, but DTS doesn’t seem to work. Any other ideas? ———-
T Kelley
What was the error when trying to import the rows? Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I don’t think you can use ADO to import CLOB over. You have to use JDBC since java driver deal with CLOB explicity. I suggest you use JDBC connect to SQL 2000 and import them over. what kind of CLOB you have in there ? Text ? image ? I think SQL2000 has BLOB you could use that for clob datatype. This is not a simple process may require some advance queries. I have never done this myself but I ask my coworker and they said JDBC is the starter and that is what I know. May the best cheaters win