创建临时表时，系统内存不足，无法运行此查询 [英] There is insufficient system memory to run this query when creating temporary table

问题描述

StringBuilder query = new StringBuilder();
                    query.Append("CREATE TABLE #Codes (Code nvarchar(100) collate database_default ) ");
                    query.Append("Insert into #Codes (Code) ");
                    int lengthOfCodesArray = targetCodes.Length;
                    for (int index = 0; index < lengthOfCodesArray; index++)
                    {
                        string targetCode = targetCodes[index];
                        query.Append("Select N'" + targetCode + "' ");
                        if (index != lengthOfCodesArray - 1)
                        {
                            query.Append("Union All ");
                        }
                    }
  query.Append("drop table #Codes ");

on: cmd.ExecuteReader() 我明白了

on: cmd.ExecuteReader() I get

创建临时表时系统内存不足，无法运行此查询

There is insufficient system memory to run this query when creating temporary table

But weird thing is that, when I have 25k codes is ok, when 5k I get this error. 

初始大小为 262 MB.

Initial size is 262 MB.

每个代码的长度平均为 15.

Lengt of each code is average 15.

推荐答案

这产生了一个巨大的声明，当然它最终失败了.

This produces one giant statement, and of course it fails eventually.

您应该一次执行一个 INSERT(没有 UNION ALL)，至少在优化之前是这样.

You should do your INSERT one at a time (no UNION ALL), at least until it's time to optimize.

我有一种感觉，您的最终答案将涉及BULK INSERT，但我对您的应用程序的了解还不够确定.

I have a feeling that your ultimate answer is going to involve BULK INSERT, but I don't know enough about your application to be sure.

这篇关于创建临时表时，系统内存不足，无法运行此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！