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
MS, MCDBA, OCA, CIW

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
MS, MCDBA, OCA, CIW

What was the error when trying to import the rows? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
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

]]>