Click here to Skip to main content
14,972,762 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(...)
Posted
Updated 5-Jun-18 20:56pm
v2

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

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


Then use a stored procedure to transform your staging data to the final table.
   
Comments
Matrimony 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
   
LOL!
Matrimony 7-Jun-18 2:36am
   
what i have are a batch of insert statement,i don't have a datatable as the example.
Mycroft Holmes 7-Jun-18 3:28am
   
And your batch of insert statements do not work in a timely manner! So I have proposed a solution that I know works as we use it regularly to insert many 100 of thousands of rows in a single call to the database.

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