DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: load data from sql server

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

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

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    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.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    A couple things to check into:

    - SQLServer can replicate to Oracle:
    [url]http://support.microsoft.com/support/SQL/Content/SQL65/repl.asp?LN=EN-US&SD=gn&FR=0&qry=Oracle&rnk=17&src=DHCS_MSPSS_gn_SRCH&SPR=SQL[/url]

    - DTS can move data directly to an ODBC datasource (such as one connected to your Oracle database):
    [url]http://support.microsoft.com/support/SQL/Content/inprodhlp/_data_transformation_services.asp?LN=EN-US&SD=gn&FR=0&qry=Oracle&rnk=21&src=DHCS_MSPSS_gn_SRCH&SPR=SQL[/url]

    - You can set up a linked server to your Oracle database and write the data directly to your tables:
    [url]http://support.microsoft.com/support/SQL/Content/inprodhlp/_setting_up_a_linked_server.asp?LN=EN-US&SD=gn&FR=0&qry=Oracle&rnk=29&src=DHCS_MSPSS_gn_SRCH&SPR=SQL[/url]

    - 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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  7. #7
    Join Date
    Jan 2001
    Posts
    61

    Thumbs up

    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....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width