如何执行3GB SQL 文件（Microsoft SQL Server）？
我有一个很大的 SQL 文件，无法装入内存，需要针对 Microsoft SQL Server 2008 执行。 sqlcmd.exe 工具似乎总是首先将其加载到内存中，这在本例中是不可能的。 有任何想法吗？
不幸的是，我无法拆分脚本，因为它是由 Red Gate 出色的 SQL Data Compare 生成的。 整个脚本是一项大事务，我想保持这种状态。 我从来没有想过拥有一个巨大的脚本是不寻常的，因为拥有大量数据在数据库世界中很常见。 该脚本大小为 3GB。
I have a big SQL file that does not fit into memory and needs to be executed against Microsoft SQL Server 2008. It seems that the sqlcmd.exe tool always loads it into memory first which is impossible in this case. Any ideas?
Unfortunately, I can't split the script because it is generated by Red Gate's excellent SQL Data Compare. The entire script is one big transaction and I want to leave it that way. I had never thought that having a gigantic script is unusual because having a lot of data is common in the database world. The script is 3gb in size.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
RedGate 的 SQL Compare 可以选择直接执行语句，而不是生成 SQL 脚本并稍后执行。 是否有什么原因这不起作用 - 换句话说，是否有原因您需要 SQL 脚本并且无法使用应用程序的“立即同步”功能？
RedGate's SQL Compare has an option to execute the statements directly, instead of generating a SQL script and executing it later. Is there a reason this wouldn't work - in other words, is there a reason you require a SQL script and can't use the application's "synchronize now" functionality?
几个月前我遇到了这个问题。 我每周和每月使用 SQLDataCompare 为我们的几个目录数据库生成同步脚本，它们通常大于 500MB。 我的解决方案是编写一个 VBscript，将更新脚本分成 50 到 1000 个命令批次。 这种方法的问题是，如果数据库更新中途出现问题，则无法回滚所有更改。
I ran into this problem a few months ago. I generate sync scripts with SQLDataCompare on a weekly and monthly basis for several of our catalog databases and they are routinely larger than 500MB. My solution was writing a VBscript that chops the update script into 50 to 1000 command batches. The problem with this approach is losing the ability to roll back all changes if something breaks halfway into your database update.
什么/谁创建了 SQL 脚本？ 获取创建文件的任何内容，通过事务或语句（取决于文件的结构）将脚本拆分为逻辑块。 如果源不能做到这一点，那么就编写一个脚本来逻辑地分割文件。
What/who created the SQL script? Get whatever created the file to split the script up into logic chunks, by either transaction or statement (depending on how the file is structured). If the source can't do this, then whip up a script to split the file up logically.
如果那么大，则脚本要么太复杂，要么是重复的。 无论哪种情况，正如其他人所建议的，唯一明智的做法是将其分解为可管理的块。
这是一次性练习还是定期活动？
If it is that big, the script is either too complex or is repetitive. In either case, as others have suggested, the only sensible thing is to break it down into manageable chunks.
Is this a one-off exercise or a regular event?
我以前遇到过这个问题，脚本中有一个与 OpenXML 一起使用的巨大 XML 字符串。 实际的 SQL 相当少，只是更新表中的一些值。
我最终将数据（以块的形式）插入到临时表中，直到 XML 中的所有信息都被存储为止。 然后我运行了我的更新语句。
发布更多数据后添加：
您可能希望在工具中选择大块，并让 SQL 数据比较以块的形式生成脚本。 这样你就可以得到交易。 您只需突出显示范围并按空格键即可选择较大的部分。
I've had this problem before where the script had an enormous XML String that was being used with OpenXML. The actual SQL was rather minimal, updating some values in a table.
I ended up inserting the data (in chunks) into a temporary table until all the info that was in the XML was stored. Then I ran my update statement.
Added later after more data got posted:
You may want to select large chunks in the tool and have SQL Data compare generate the scripts in chunks. That way you get the transactions. You can select large sections by simply highlighting a range and hitting the space bar.
1-800-redgate-support.....
或者
1-800-redgate-support.....
or
据我了解，SSMS 是 32 位的，因此它无法加载超过 1.5-2 GB 的脚本。 您可以在 SQLCMD.exe 中运行该脚本，但随后可能会因事务大小而遇到问题 - SqlCmd 会将整个事务保留在内存中。 因此，您可以在 SQL Data Compare 中执行的操作是进入选项并使用“拆分事务”，这可能会有所帮助。
SQL Data Compare 还将对 BLOB 进行部分更新，这将解决“巨大的 BLOB”问题。
当然，如果基于最新版本的数据比较。 有些版本可能没有这些功能。
另一种选择可能是使用 SQL Compare 创建文件夹的架构脚本，然后使用 SDC 将数据同步到该文件夹中。 然后每个表都有一个文件，而不是一个庞大的文件。
希望这可以帮助。
The way I understand it, SSMS is 32-bit so it can't load a script over 1.5-2 GB. You can run the script in SQLCMD.exe, but then you may run into problems because of the transaction size -- SqlCmd will keep a whole transaction in memory. So what you can do then in SQL Data Compare is go into the options and use "split transactions", which may help.
SQL Data Compare will also do partial updates to BLOBs, which will solve the "enormous BLOB" issue.
This of course if based on the latest version of Data Compare. Some versions may not have these features.
Another option may be to use SQL Compare to create a schema script to a folder, then use SDC to sync the data into that folder. Then you have a file for each table rather than one massive file.
Hope this helps.
使用
sqlcmd命令示例：
Use
sqlcmdcommandExample: