load data from sql server

Hi

Does anyone know how to transfer data of one user from sql server to oracle 8i :-?

I haven't used sql server but I'm sure you could just go with the flatfile/sql loader solution unless there is an easier way.

hmmm I know with DTS (sql server utility) we can extract data in ascii format, howeverr I am not sure if the text format are ok for sql loader to load.... Anyone knows?
Also is there anyway to extract data from oracle into flat files other than using spool command in sql plus? Such as with DTS we could extrcat data from sql server into flat files.

cheers

I haven't used sql loader but i hear it's very flexible. If worse comes to worse, you take the sql server ascii file and write a script to read each line, generate an insert statement, then run that on the database. Just make sure you put in a batch size if it's a large quantity of data.

As far as taking data without spooling, you could probably use pl/sql and utl_file, or a similar program written in any other language you can connect to the database with. Perl, C, etc.

Or possibly you could export the table, then import with show=y and copy the insert statements to another file, or parse and turn it into a delineated file instead of insert statements if that's what you need.

A couple things to check into:

- SQLServer can replicate to Oracle:
http://support.microsoft.com/support...n_SRCH&SPR=SQL

- DTS can move data directly to an ODBC datasource (such as one connected to your Oracle database):
http://support.microsoft.com/support...n_SRCH&SPR=SQL

- You can set up a linked server to your Oracle database and write the data directly to your tables:
http://support.microsoft.com/support...n_SRCH&SPR=SQL

- And lastly, you could simply use Access, link to the tables in both database and write some quick SQL to copy it that way.

Personally, I would only fall back to moving data via flat files as a last resort (or if you want to preserve that data for posterity). We have moved past that, guys :)

- Chris

basically the situation is as follows

there are 2 websites, one running sql server 7.0 and the other oracle 8i, what I have to do is periodically, every 3 days or so pass some data from a few tables in Oracle to SQL Server. We are not allowed to use a link because SQL Server is housed in a server where quite a few server is using that same housing so due to security problems linking wont be possible.
So I guess flat file would be the only solution?

It's very straight to transfer data from SQLServer to Oracle
Step1:
Create a ODBC Connection (System DSN) for Oracle ODBC Driver

Step2:
In SQL Server open the IMPORT AND EXPORT DATA utility. You can do this directly as well as with ENTERPRISE MANAGER

-Choose a Data source with suitable Database name and type of authentication
-Choose a destination .IN THE DESTINATION DROP DOWN LIST PICKUP THE OTHER(ODBC DATA SOURCE) option
-Pickup the option to copy data from the source database
- Select the Tables you want to transfer the data

That's it....