Timeline for sqlcmd to run large SQL file causes high CPU usage
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 29, 2011 at 13:26 | comment | added | Marian | @Jean-FrançoisBeauchamp: nope, BCP is a command line tool used to transfer tables' data in files and then back in tables. It handles binary files, not sql files. Instead of generating comparison scripts with a diff tool, you could build a more general script that takes a tables list, goes through the differences between each table in both dbs, builds staging tables for the differences, use Bcp to generate binary files.. than use those files in the second environment. This is a bit of a hassle at the beginning, to build the flow, but it will work much faster than generating huge SQL files. | |
| Sep 28, 2011 at 16:01 | comment | added | Eric Humphrey - lotsahelp | You can also use SSIS or the import / export wizard (which uses ssis) to transfer the data from your local copy to the remote copy. See if your tool has a batch size option as well. Furthermore, data compare might not be the best approach for this unless it's a one time deal. | |
| Sep 28, 2011 at 15:38 | comment | added | Jean-François Beauchamp | Can BCP handle an SQL script? Because I don't think the tool I am using can generate something else than SQL scripts. Here how I solved my problem: The file was generated by xSQL Data Compare, and it would have been quite time consuming to edit it to add more GO statements. However, my copy of xSQL Data Compare is able to run the script on my local instance of SQL Server Express, so I backed up the remote database I was working on and restored it locally. I was then able to run the script with the tool on my local machine and to bring back that database to the remote server. | |
| Sep 28, 2011 at 15:35 | comment | added | Marian | Glad to be of service. I'd still suggest that instead of doing a big SQL file (even with small batches), you'd get huge improvements by going the BCP import route. | |
| Sep 28, 2011 at 15:32 | comment | added | Jean-François Beauchamp | You were right. It is a script to load data in the database, and although there are a few 'GO' at the beginning of the script where triggers are being disabled and constraints are dropped, there is only one GO command for the big bunch of INSERT statements. | |
| Sep 28, 2011 at 15:28 | vote | accept | Jean-François Beauchamp | ||
| Sep 28, 2011 at 9:09 | history | answered | Marian | CC BY-SA 3.0 |