使用 SSMS 将 1000 行插入 SQL 表
我有一个带有插入语句的 SQL 脚本，用于插入 1000 行（大约 12000 行）。 当我尝试在 SSMS 中运行脚本时，一段时间后它会抛出“内存不足”异常。
"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."
我的 Vista 上有 SQL Server 2008，内存为 3GB。
任何想法或指示将不胜感激！
I have a sql script with insert statements to insert 1000s of rows (12000 approx.).
When i try running the script in SSMS, it throws 'Out of memory' exception after a while.
"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."
I have SQL Server 2008 on Vista with 3gb RAM.
Any thoughts or pointers would be appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
您将必须拆分命令。最简单的方法是每 10 行左右添加一个 GO。
基本上，SSMS 尝试将所有文本加载到单个 SqlCommand.CommandText 中并执行它。那是行不通的。
你需要得到它来批量处理它们。 GO 是 SSMS 中的一个简单分割点，它将一直到该点并执行它，然后继续。
线路1
线路2
...
GO
LINE11
LINE12
这将在 2 个 SqlCommands 中运行到数据库。
如果您需要它们全部在单个事务中运行，您可能需要编写一个命令行应用程序来加载每一行并在事务中执行它。我认为您不能在 SSMS 中跨执行拆分事务。
您还可以构建一个 SSIS 包，但这需要大量工作，我不推荐这样做，除非您需要经常重复此过程。
You will have to split up the commands. The easiest way is to add a GO every 10 lines or so.
Basically the SSMS is trying to load all your text into a single SqlCommand.CommandText and execute it. That won't work.
You need to get it to batch them. GO is an easy split point in SSMS where it will take up to that point and execute it, then continue.
LINE1
LINE2
...
GO
LINE11
LINE12
That will be run in 2 SqlCommands to the database.
If you need them all run in a single transaction you will probably have to write a command line app to load each line and execute it within a transaction. I don't think you can split transactions across executions within SSMS.
You could also build an SSIS package, but that is a LOT of work and I don't recommend it unless you need to repeat this process a every so often.
System.OutOfMemoryException 是 CLR 异常，而不是 SQL Server 异常。 SQL 会引发错误 701，而且它不会因为简单地执行一些插入而耗尽内存。
您收到 CLR 异常的事实表明问题可能出在 SSMS 本身。确保您的脚本不会向 SSMS 返回虚假结果集和消息。另外，尝试从 sqlcmd 执行脚本。
System.OutOfMemoryException is a CLR exception, not a SQL Server exception. SQL would raise an error 701, and besides it would not run out of memory from simply executing some insrts to start with.
The fact that you get a CLR exception indicates that the problem is perhaps in SSMS itself. Make sure your script does not return spurious result sets and messages to SSMS. Also, try executing the script from sqlcmd.
这是一篇旧帖子，但似乎没有人发现明显的问题。所以我想发布一个回复，可以帮助人们寻找答案。在 SQL Server\Properties\Memory 下，有一个“每个查询的最小内存”设置。您可以暂时提高此数字以帮助增加 GO 语句之间的记录数。就我而言，我将其提高到 5000（10000 导致系统内存不足错误，不好），所以我选择了 5000，经过几次测试后，我发现我现在可以导入大约 20,000 行，所以我每 20,000 行放置一个 GO 语句（大约花费了 10 分钟），我能够在一个查询中导入超过 200,000 行。
祝你编码愉快，罗布
This is an old post but no one seems to have identified the obvious issue. So I wanted to post a reply that may help someone searching for answers. Under SQL Server\Properties\Memory there is a setting for Minimum Memory Per Query. you can raise this number temporarily to help increase the number of records between the GO statements. In my case I raised this to 5000 (10000 caused a system out of memory error, not good) so I settled for 5000, after a few tests I found that I could now import about 20,000 rows so I placed a GO statement every 20,000 rows (took about 10 minutes) and I was able to import over 200,000 rows in one query.
Happy coding, Rob
您可能需要插入奇怪的
提交，以免填满事务日志。You may need to insert the odd
commitso as to not fill up the transaction logs.修复结果是批量运行插入语句。我做了一些实验来确定在事务中可以执行的最大插入次数。
因此，对我有用的修复方法是将插入语句分成更小的组（5000）并将它们标记为带有提交的事务。对所有组重复相同的操作。它的工作没有给出“内存不足”异常。
但更糟糕的是，我花了 2 个小时才插入 50,000 行，这让我感到惊讶。有没有什么办法可以让我在更短的时间内完成它？
感谢您的所有评论。特别是 paxdiablo 和 Jason Short 指出了解决方案，以及其他人找出了原因。
The fix turned out to be running the insert statements in batches. I had do a little bit of experimenting to identify the maximum number of inserts i could do in a transaction.
So, the fix that worked for me was to split the insert statements into smaller groups (5000) and mark them as transaction with commit. Repeated the same for all the groups. It worked without giving 'out of memory' exception.
But on the darker side, it took me 2 hrs to insert the 50,000 rows, which took me by surprise. Was there a way i could have done it in a shorter time?
Appreciate all your comments. Specifically paxdiablo and Jason Short for pointing to the solution and everyone else for reasoning out the cause.
确保使用 SET NOCOUNT ON；
而不是：
您也可以尝试使用以下内容构建脚本，该脚本作为单个隐式事务工作（避免需要在每次插入之间运行 COMMIT）：
但是，如果整个脚本仍然太大而无法让 SSMS 处理（我想您正在插入一些冗长的字符串），那么您唯一的选择就是像其他人所建议的那样......拆分您的脚本或使用不同的工具。
Make sure you use SET NOCOUNT ON;
Instead of:
You could alternatively try building your script using the following, which works as a single implicit transaction (avoiding the need to run COMMIT between each insert):
However, if the overall script is still too big for SSMS to handle (I imagine you are inserting some lengthy strings), then your only options are as the others have suggested ... split up your script or use a different tool.
我通过调用其中的存储过程来使用游标插入
但因内存不足异常而失败
我将 go 放在最后一个存储过程中，它提高了一些性能
最后在管理中将网格视图更改为文本视图工作室 效果很好。
I was inserting using cursor by calling stored procedure inside it
but was failing for out of memory exception
I put go at the last Stored procedure it increased some performance
Last changed grid view to Text view in management studio It worked fine.
打开>任务管理器>关闭SSMS服务
然后再次打开SSMS studio
现在您可以运行查询。
Open > Task Manager > close SSMS Service
Then again open SSMS studio
Now you can run your query.