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,

What version of sql server? DTS or SSIS packages are the way to go.

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.

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