Post reply

Import and export data

  • November 24, 2003 at 9:42 am

    #483400

    So is there any way to copy objects from non sql server database to sql server database by program.

    Thanks

  • November 24, 2003 at 3:28 pm

    #483460

    Not the would be anymore managable than DTS. Informix isn't one of the mainstream DBMS's that third-party developers provide a lot of native support for.

    In the DTS package what data source are you using, 'Other (ODBC Data Source)', 'OLE DB provider for ODBC', or something else?

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • November 24, 2003 at 3:59 pm

    #483466

    I am using 'Other (ODBC Data Source)'

  • November 24, 2003 at 4:02 pm

    #483467

    For the import from A to B, do you have any transformations, or is it a straight Datapump from one table to the other?

    If not, create a staging table that you can import the data straight into. Then use stored procedures to modify the data as it goes into the final tables.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • November 25, 2003 at 5:30 am

    #483520

    I agree with phillcart's postings, I transfer 7GB (46 tables) daily from a 3rd party database using ODBC and DTS is the fastest method. I create one DTS package per table (makes life easier to rerun in case of failure) and scheduled a job to execute each package in turn.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 25, 2003 at 8:11 am

    #483544

    Phil, Ans to your question

    For the import from A to B, do you have any transformations, or is it a straight Datapump from one table to the other?

    Nothing just table to table

    David,

    How long it takes to transfer 7GB. Are you deleting before import data, if so what happens if the job failed after deleting the data. My intension is do in one transaction.

    Thanks.

  • November 25, 2003 at 8:22 am

    #483547

    quote:

    How long it takes to transfer 7GB. Are you deleting before import data, if so what happens if the job failed after deleting the data. My intension is do in one transaction.

    3hrs. I check that the database table is available (select ... from ...), if OK I then truncate the destination table and then do the transfer. I do column to column data pump with occasional column ActiveX to correct erroneous data. If one of the transfers fails I rerun the DTS for the table in question.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 3, 2003 at 4:47 pm

    #484549

    For B to C server you can use the OPENROWSET by program although it may be slow. See BOL for OPENROWSET

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply