Copy Tables Another Sql Istance

  • Hi,

    I have this task, I have to copy from one database into a remote all record which contain a specific value into a specific column. (the remote Db destination tables are already created)

    I have already identified all tables to copy (about 3000 with 60Milions of records), now I need your suggestion to implement the most performing way to copy records.

    If most performing, the destination database can be placed on the source Sql instance, I can move the database on the destination instance after I have copied all records.

    Do you have any suggestion for me?

    Thanks

    Regards

  • vincenzo.capelli (9/19/2015)


    Hi,

    I have this task, I have to copy from one database into a remote all record which contain a specific value into a specific column. (the remote Db destination tables are already created)

    I have already identified all tables to copy (about 3000 with 60Milions of records), now I need your suggestion to implement the most performing way to copy records.

    If most performing, the destination database can be placed on the source Sql instance, I can move the database on the destination instance after I have copied all records.

    Do you have any suggestion for me?

    Thanks

    Regards

    Quick thought, probably the easiest solution is to do this on the same instance as the source because then you can do straight select into without any ETL getting in the way.

    😎

  • How long to copy the database from Remote to Local, and then to copy it back again afterwards?

    If that is a long time (big database, slow network) then one way would be to export all the required data using e.g. BCP and using NATIVE FORMAT files. If these are exported pre-sorted by clustered key order then the re-import at the far end can provide a hint that the file is presorted which increases import speed.

    But ... the DEV time to create scripts for lots of tables and queries to only export the desired records will not be trivial.

Viewing 3 posts - 1 through 2 (of 2 total)

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