Click here to Skip to main content
15,434,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In my application,it will generate 20,000 rows of insert statement dynamically, and submit it to sql server and run.
When these insert statements're running, an error occurs
There is insufficient system memory in resource poll 'default' to run this query.

is there an efficient way to run these insert statements?

What I have tried:

is there an efficient way to run these insert statements?

20,000 rows of insert statement
like this :
insert into table1(....) values(...)
insert into table2(....) values(...)
insert into table20000(....) values(...)
Updated 5-Jun-18 20:56pm

1 solution

I use a staging strategy where I BULK COPY the data into SQL Server if I have that sort of volume. This entails:

Have a staging table with fields matching your source data column names
All fields in the staging table should be varchar
Read in the source data
Get an empty record set from the database for the staging table
insert the source data into the empty DataTable
Bulk Copy the DataTable into sql server

private int BulkCopy(DataTable dtTable, string sTableName, SqlConnection oConn)
				SqlBulkCopy oBC = new SqlBulkCopy(oConn);
				oBC.BulkCopyTimeout = 60000;
				oBC.DestinationTableName = sTableName;
				return dtTable.Rows.Count;
			catch (Exception ex)
				throw ex;

Then use a stored procedure to transform your staging data to the final table.
Share this answer
855 6-Jun-18 2:56am    
this doesn't wok for me
CHill60 6-Jun-18 3:43am    
"doesn't work" isn't helpful. What explicit problems did you encounter?
Mycroft Holmes 6-Jun-18 4:19am    
Solution too complex, codez not explicit enough - what you expect him to THINK!!!
CHill60 6-Jun-18 7:14am    
855 7-Jun-18 2:36am    
what i have are a batch of insert statement,i don't have a datatable as the example.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900