Execute batch insert statement in SQL SERVER

Question

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 3-Jun-18 17:56pm

855

Updated 5-Jun-18 20:56pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mycroft Holmes · Answer 1 · 2018-06-03T23:27:00

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.