How do I run a large script with many inserts without running out of memory?

Question:

I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run?

Context:

• Added some new data fields to make an app play nice with another app

the client uses.

• Got a spreadsheet of data from the client full of

data that mapped current data items to values for these new fields.

• Converted spreadsheet to insert statements.

• If I only run some of the statements it works but the entire script does not.

• No. There are no typos.

If there is a different way I should be loading this data feel free to chastise me and let me know.

The maximum batch size for SQL Server 2005 is 65,536 * Network Packet Size (NPS), where NPS is usually 4KB. That works out to 256 MB. That would mean that your insert statements would average 5.8 KB each. That doesn't seem right, but maybe there are extraneous spaces or something unusual in there.

My first suggestion would be to put a "GO" statement after every INSERT statement. This will break your single batch of 45,000 INSERT statements into 45,000 separate batches. This should be easier to digest. Be careful, if one of those inserts fails you may have a hard time finding the culprit. You might want to protect yourself with a transaction. You can add those statements quickly if your editor has a good search-and-replace (that will let you search on and replace return characters like \r\n) or a macro facility.

The second suggestion is to use a Wizard to import the data straight from Excel. The wizard builds a little SSIS package for you, behind the scenes, and then runs that. It won't have this problem.

StackExchange Database Administrators Q#16763, answer score: 18