Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Unanswered: Question on 'load/replace" in SQL Server

    I have to periodically overwrite selected tables in one environment (Prod) with the contents of the same named tables from another environment (Staging).

    There are about 200 tables involved.

    In SQL Server what would be the best way to do this and minimize logging?

    Thanks in advance.
    Gerry

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I usually do this:

    0. bcp out all data from source
    1. disable FKs as appropriate in dest
    2. truncate/delete from tables in dest
    3. bcp in all data to dest
    4. re-enable FKs in dest

    You could probably also use DTS/SSIS for this, but I prefer bcp because I like the cmd line.

    EDIT: one thing I forgot: step 2.1 should be to drop all indexes in dest, then at 4.1 recreate indexes. with indexes dropped the data will import much faster.
    Last edited by jezemine; 12-22-06 at 19:06.

Posting Permissions

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