Post reply

Best Way to move data between databases?

  • June 24, 2005 at 8:41 am

    #60773

    Just wanted to have views on-

    What is the best way to move a few(8-10) tables from one database to other on the same server.

    ---1. To use a DTS packege directly to delete the destination tables and turn identity insert on and transform the data?

    ---2.  Create a package executing following steps one by one on success-

           *Drop all foriegn keys at destination tables

           *Drop all primary keys at destination tables

           *Truncate all destination tables

           *Transform them one by one

          

           *recreate primary keys

           *recreate foriegn keys

    Or any other method?

    Thanks

     

  • June 24, 2005 at 9:00 am

    #568292

    If you delete the tables, you won't have to worry about turining IDENTITY_INSERT on. you could just use the Copy SQL Server objects task in DTS.

    If you want to delete from the destination tables, then insert the data, you will have to worry about IDENTITY_INSERT, as well as possibly reseding the identity fields.

     

     

  • June 24, 2005 at 11:09 am

    #568340

    I'd use method 2 but omit dropping and recreating the primary keys.

    Greg

    Greg

  • June 27, 2005 at 9:49 pm

    #568802

    how about stored procs?

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

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