Results 1 to 5 of 5

Thread: moving data

  1. #1
    Join Date
    Feb 2004
    Posts
    490

    moving data

    We are moving data from one server to another. The table structures are somewhat different but data is bound to strict constraints from a business and security point of view.

    So right now I've created the queries that produce the right data, but they are somewhat large. I'm also supposed to be able to bundle the whole lot and have it executed 'as is' on the production environment.

    So I put each query in a separate file and I'm using sqlcmd for exporting. I made a script to execute all queries and dump the data into files. However, I'd rather use bcp instead so I can use format files for both the export and import. The binary data especially proves a bit of a hassle on the other side.

    Do you guys have a best practice on this?

    thanks,

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    794
    What version of sql server? DTS or SSIS packages are the way to go.

  3. #3
    Join Date
    Feb 2004
    Posts
    490
    Thank you for your suggestions. We're using 2005 and 2008. Personally I'm not too keen on SSIS packages, but that's probably me: I've had some bad experience staying in control and finding out what's wrong. I'm more of a script person anyway.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    794
    Quote Originally Posted by Kaiowas View Post
    Thank you for your suggestions. We're using 2005 and 2008. Personally I'm not too keen on SSIS packages, but that's probably me: I've had some bad experience staying in control and finding out what's wrong. I'm more of a script person anyway.
    With SSIS you can have error log reporting which should give you a record for each failed import. I used this task to write out the specific records that didn't import so they could be corrected and resent.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    PowerShell might be more your speed then. Depending on what conferences you've attended you might be able to access one of the SSIS versus PowerShell Smackdown presentations, but even if you can't do that you can at least review http://mem-pass.org/summits/2011/pdfs/BIA-100.pdf for some idea of what is possible.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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