Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Unanswered: copy multiple tables from Sybase server/db to another.

    Hi Folks,

    I am new to Sybase and would like to copy 35 tables from one Sybase server/db to another.
    Pls suggest any approaches that might be possible. I am currently doing it in SAS but would like to do it in SAS.

    One approach I know is use bcp to write tables to files and then write files as input to another database.Not sure if all the properties of the source tables will be there?

    Is there any better way to copy to have a better copy?

    Regards

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    1 The fastest method is:
    - if necessary, truncate table on target server
    - drop indices on table on target server
    - bcp-out on source server (to o/s files)
    - bcp-in the o/s files to target server
    - create indices on table on target server

    1.a Obviously the DDL of the tables must be identical. bcp has many options and features which you need to read up oon, following which you can make it even faster.

    1.b You can bcp-in with the indices intact, but that is equivalent to seril inserts, causes the expected logging, tran log problems, etc.

    1.c If you are populating the target table incrementally, you do not need to truncate it, but you do need a method of extracting only those rows since the last extraction. Likewise, if the existing target population is large, and the extract population is small, you may not need to drop/create the indices. Test for yourself and fid out what the timings are.

    2 The slow method, but fully supported and results in a more "online" or up-to-date copy, is to create a proxy table on the target server, which points to the source server/table. Then you simply insert target_table select from source_table where rows not in target_table. This needs:
    - the servers connected for remote access
    - Component Integration Services configured properly
    - and of course creates logging in systemdb, so that needs config as well.

    I haven't worked with SAS-Sybase for a few years, in those days it used to do [2], not [1], and thus was pretty slow.
    Last edited by Derek Asirvadem; 08-31-09 at 21:15.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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